Update the column which retain the old values using SAS SQL

Question

Need to update the column in the master file, say Column1 has A & B values.

I have a different set of a file (mapping file) which has 2 different columns Column1 has only A values & Column2 has only B values which have unique values.

My requirement is to replace only A values to B and presented B values must retain same as in the column in the master file.

Please help me to write the update statement in SAS SQL.

I have tried this,

PROC SQL;
UPDATE MASTERTABLE K
SET COLUMN1 = (SELECT L.COLUMN2 FROM MAPPINGFILE L WHERE K.COLUMN1= L.COLUMN1);
QUIT;

For better understanding,attach a sample dataset of what your data looks like and how you want your output to be? — G.Arima, Dec 13 '17 at 07:21

score 0 · Answer 1 · answered Dec 13 '17 at 20:14

It sounds like you want to minimally track the master tables pre-map values as well as perform a mapping operation. If this is not the case please edit the question with more detail and sample data.

The SQL exists operator will let you specify criteria that selects, for update, only those rows that need a mapping action.

data have_master;
  do row = 1 to 100;
    current_value = byte(65 + mod(row,26));
    prior_value = ' ';
    output;
  end;
run;

data have_map;
  length from_value to_value $1;
  input from_value to_value;
datalines;
A a
B d
Z K
run;


proc sql;
  update have_master
  set 
    prior_value = current_value
  , current_value = (select to_value from have_map where have_map.from_value = have_master.current_value)
  where
     exists (select * from have_map where have_map.from_value = have_master.current_value)
;

%let syslast=have_master;

I say minimally track because a subsequent use of the query can overwrite a prior_value assigned previously.

Update the column which retain the old values using SAS SQL

1 Answers1