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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.