CODEWITHSUNDEEP
sas
athresh
athresh

Reputation: 553

Update one dataset with another without using PROC SQL

I have the below two datasets

Dataset A

id age  mark
1  .    .
2  .    .
1  .    .

Dataset B

id age mark
2  20  200
1  10  100

I need the below dataset as output

Output Dataset

id age mark
1  10  100
2  20  200
1  10  100

How to carry out this without using PROC SQL i.e. using DATA STEP?

Upvotes: 2

Views: 2164

Answers (2)

user1965813
user1965813

Reputation: 671

I tried this and it worked for me, even if you have data you would like to preserve in that column. Just for completeness sake I added an SQL variant too.

data a;
input id a;
datalines;
1 10
2 20
;

data b;
input id a;
datalines;
1 .
1 5
1 .
2 .
3 4
;

data c (drop=b);
merge a (rename = (a=b) in=ina) b (in = inb);
by id;
if b ne . then a = b;
run;

proc sql;
create table d as
select a.id, a.a from a right  join b on a.id=b.id where a.id is not null
union all
select b.id, b.a from a right join b on a.id = b.id where a.id is null
;
quit;

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

There are many ways to do this. The easiest is to sort the two data sets and then use MERGE. For example:

proc sort data=A;
   by id;
run;
proc sort data=B;
   by id;
run;

data WANT;
  merge A(drop=age mark) B;
     by ID;
run;

The trick is to drop the variables you are adding from the first data set A; the new variables will come from the second data set B.

Of course, this solution does not preserve the original order of the observations in your data set AND only works because your second data set contains unique values of id.

Upvotes: 3

Related Questions