topic Re: How do I update a SAS dataset using another dataset on basis of a common field in SAS Programming

How do I update a SAS dataset using another dataset on basis of a common field

PoojaP1 — Thu, 28 Apr 2016 17:15:41 GMT

Hello, Could anyone help me on how to update observations in an existing SAS dataset, using another dataset. Like, I have 2 datasets :-

DATA Data_one;

input id prod $ v1 v2 v3 v4 v5;

datalines;

100 abc 1 0 0 0 0

102 asd 1 1 0 0 0

105 0 1 0 0 0

100 pqr 1 0 0 0 0

102 sdf 0 1 0 0 0

103 rty 1 1 0 0 0

;

run;

DATA Data_two;

input id v3 v4 v5;

datalines;

100 1 0 1

102 0 1 0

103 0 0 0

105 1 0 0

;

run;

I want the fields v3,v4,v5 of Data_one to be updated according to Data_two on the basis of the ID field. I tried using merge statement, but that updated only the first observation for each group. For ex. in Data_one, I want for ID 100 the fields should get updated as 1,0,1 under 'abc' as well as under 'pqr', and not only under 'abc'. Please help.

Thank you in advance!

Re: How do I update a SAS dataset using another dataset on basis of a common field

Reeza — Thu, 28 Apr 2016 17:20:21 GMT

Have you tried an update statement?

Re: How do I update a SAS dataset using another dataset on basis of a common field

PoojaP1 — Thu, 28 Apr 2016 17:22:49 GMT

Yes, I tried using that. But, it updated only the very first observation of each group of the BY variable. Like, for ID 100 it updated only the first observation and not the second one.

Please help. Thank you!

Re: How do I update a SAS dataset using another dataset on basis of a common field

Astounding — Thu, 28 Apr 2016 17:26:25 GMT

Here's an approach. Assuming both data sets are first sorted by ID:

data want;

merge data_one (in=in1) data_two (in=in2 rename=(v3-v5=w3-w5));

by id;

if in1;

if in2 then do;

v3=w3;

v4=w4;

v5=w5;

end;

drop w3-w5;

run;

Re: How do I update a SAS dataset using another dataset on basis of a common field

Reeza — Thu, 28 Apr 2016 17:29:21 GMT

Can you have multiples in both files or only the first file?

Re: How do I update a SAS dataset using another dataset on basis of a common field

PoojaP1 — Thu, 28 Apr 2016 17:30:44 GMT

@Reeza : No, only the first dataset contains multiples.

Re: How do I update a SAS dataset using another dataset on basis of a common field

Reeza — Thu, 28 Apr 2016 17:36:28 GMT

You'll have to explicitly code it if you have multiples.

A data step or SQL will work. It can be a SQL join or an update.

Here's a SQL join example, others have posted a data step solution that will work as well.

proc sql;
create table want as
select a.id, a.prod, a.v1, a.v2, coalesce(b.v3, a.v3) as v3, coalesce(b.v4, a.v4) as v4, coalesce(b.v5, a.v5) as v5
from data_one as a
left join data_two as b
on a.id=b.id
order by 1,2;
quit;

Re: How do I update a SAS dataset using another dataset on basis of a common field

PoojaP1 — Thu, 28 Apr 2016 17:42:48 GMT

Thanks a lot for your quick help.

Thanks to both of you 🙂