Hi,
I want to compare the table1 column (‘Var’) values with another table2 variable name based on key. If match then update the values from table1 ‘new_values’ to table2 which match above column.
Table1:
Key1 | Var | New_values |
1234 | Value1 | 1 |
4674 | Value2 | 9 |
5732 | Sim_ind | 6 |
6342 | Value3 | 3 |
Table2:
Key1 | Value1 | Value2 | Value3 | Sim_ind |
1234 | 3 | 2 | 2 | 3 |
4674 | 6 | 5 | 4 | 4 |
5732 | 2 | 3 | 6 | 2 |
6342 | 1 |
| 1 | 5 |
Output should be
Key1 | Value1 | Value2 | Value3 | Sim_ind |
1234 | 1 | 2 | 2 | 3 |
4674 | 6 | 9 | 4 | 4 |
5732 | 2 | 3 | 6 | 6 |
6342 | 1 |
| 3 | 5 |
Thanks
if sort isn't too expensive
data table1;
input Key1 Var:$10. New_values;
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;
data table2;
input Key1 Value1 Value2 Value3 Sim_ind;
datalines;
1234 3 2 2 3
4674 6 5 4 4
5732 2 3 6 2
6342 1 . 1 5
;
data want;
merge table2 table1;
array t(*) Value1--Sim_ind;
do _n_=1 to dim(t);
if vname(t(_n_)) eq var then do;
t(_n_)=New_values;
return;
end;
end;
drop var new_values;
run;
Here's an easy program to write. However, it only works when TABLE2 contains unique values for KEY1. Duplicates spoil the result.
proc sort data=table1;
by key1;
run;
proc sort data=table2;
by key1;
run;
proc transpose data=table1 out=changes (drop=_name_);
by key1;
id var;
var new_values;
run;
data want;
update table2 changes;
by key1;
run;
This code assumes key1 has NO duplicates. I trust your sample is good representative of your real.
data table1;
input Key1 Var:$10. New_values;
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;
data table2;
input Key1 Value1 Value2 Value3 Sim_ind;
datalines;
1234 3 2 2 3
4674 6 5 4 4
5732 2 3 6 2
6342 1 . 1 5
;
data want;
if _n_=1 then do;
if 0 then set table1;
dcl hash h(dataset:'table1');
h.definekey('key1');
h.definedata('var','New_values');
h.definedone();
end;
set table2;
array t(*) Value1--Sim_ind;
if h.find()=0 then do;
do _n_=1 to dim(t);
if vname(t(_n_)) eq var then do;
t(_n_)=New_values;
return;
end;
end;
end;
drop var new_values;
run;
if sort isn't too expensive
data table1;
input Key1 Var:$10. New_values;
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;
data table2;
input Key1 Value1 Value2 Value3 Sim_ind;
datalines;
1234 3 2 2 3
4674 6 5 4 4
5732 2 3 6 2
6342 1 . 1 5
;
data want;
merge table2 table1;
array t(*) Value1--Sim_ind;
do _n_=1 to dim(t);
if vname(t(_n_)) eq var then do;
t(_n_)=New_values;
return;
end;
end;
drop var new_values;
run;
data table1;
input Key1 Var:$10. New_values;
var=upcase(var);
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;
data table2;
infile cards expandtabs truncover;
input Key1 Value1 Value2 Value3 Sim_ind;
datalines;
1234 3 2 2 3
4674 6 5 4 4
5732 2 3 6 2
6342 1 . 1 5
;
data want;
if _n_=1 then do;
if 0 then set table1;
dcl hash h(dataset:'table1');
h.definekey('key1','var');
h.definedata('New_values');
h.definedone();
end;
set table2;
array t{*} Value1--Sim_ind;
do i=1 to dim(t);
var=upcase(vname(t{i}));
if h.find()=0 then t{i}=new_values;
end;
drop i var New_values;
run;
proc print noobs;run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.