Comparing the dataset1 and dataset2 values

[cho16]

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

[Astounding]

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;

[LinusH]

So what have you tried so far?

[novinosrin]

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;

[novinosrin · Accepted solution]

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;

[Ksharp]

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;