DATA Step, Macro, Functions and more

Comparing the dataset1 and dataset2 values

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

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

Posted in reply to novinosrin

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;

View solution in original post


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

Posted in reply to novinosrin

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 178 views
  • 1 like
  • 5 in conversation