Solved
Contributor
Posts: 39

# Comparing the dataset1 and dataset2 values

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

Accepted Solutions
Solution
‎03-01-2018 06:17 PM
PROC Star
Posts: 1,584

## Re: Comparing the dataset1 and dataset2 values

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;``````

All Replies
Super User
Posts: 6,632

## Re: Comparing the dataset1 and dataset2 values

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;

Super User
Posts: 5,852

## Re: Comparing the dataset1 and dataset2 values

So what have you tried so far?
Data never sleeps
PROC Star
Posts: 1,584

## Re: Comparing the dataset1 and dataset2 values

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;``````

Solution
‎03-01-2018 06:17 PM
PROC Star
Posts: 1,584

## Re: Comparing the dataset1 and dataset2 values

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;``````
Super User
Posts: 10,689

## Re: Comparing the dataset1 and dataset2 values

``````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;``````
☑ This topic is solved.