BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PoojaP1
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

Have you tried an update statement?

PoojaP1
Fluorite | Level 6

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!

Reeza
Super User

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

Astounding
PROC Star

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;

PoojaP1
Fluorite | Level 6

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

Reeza
Super User

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;
PoojaP1
Fluorite | Level 6

Thanks a lot for your quick help. 

Thanks to both of you 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1106 views
  • 0 likes
  • 3 in conversation