BookmarkSubscribeRSS Feed
deleted_user
Not applicable
The master data set MDS have variables ID, X and several others. The update data set UDS have variables ID and X.

New values for X is given in UDS.

If there were only one observation for each ID, I could use the code:

UPDATE MDS UDS;
BY ID;

But how do I code if there are multiple observations for each ID in the master data set, and I want all observations for an ID(identity) updated according to the update data set? Message was edited by: ErnestoC
7 REPLIES 7
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Performed a search of the SAS support websitte http://support.sas.com/ with the two SAS-hosted documentation references for your consideration. The site has a wealth of supplemental technical reference documents as well.

Scott Barry
SBBWorks, Inc.

Updating SAS Data Sets
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001304308.htm

Modifying SAS Data Sets
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001331353.htm
deleted_user
Not applicable
Before I put my question on this forum I have been searching on the Internet, but haven't found any solution.

I state that UPDATE doesn't work. I have tried with MERGE and MODIFY in different ways, but the only X values that are changed are the ones for each ID:s first record.

I hope someone can help me. Many people must have had to do the same kind of operation.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Share your SAS code with the SAS log - post a reply pasting the information. Also, you will want to include PUTLOG _ALL_; messages (dump all variables to SAS log). Of course this information may help with your own problem debugging before sending to the forum. SAS version (and service pack), operating platform are also useful. Of course, after exhausting all available debugging options, you may want to contact SAS support with your particulars, opening a SAS track on the website.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Scott Barry, are you God?
deleted_user
Not applicable
Oh God, I did it just by myself.

DATA UDS(RENAME=(X=Y));
SET UDS;
RUN;

DATA MDS;
MERGE MDS UDS;
BY ID;
RUN;

DATA MDS(DROP=Y);
SET MDS;
IF X~=Y AND Y~=. THEN X=Y;
RUN;

Good heavens!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Glad to see you were successful - for optimization, you may be able to reduce your code to a single DATA step, doing the RENAME= and DROP= on the SET statement (data set options in parentheses), and along with using the IN= on the SET for each contributing SAS file with your MERGE.

Scott Barry
SBBWorks, Inc.
Florent
Quartz | Level 8
Hi,

I think that the following solution should also meet your expectations.
The advantage is that your datasets do not need to be sorted in the same order and/or BY-variables.

Please let me know 😉

Regards,
Florent

PROC SQL;
update MDS a
set X = (select coalesce(b.X, a.X)
from UDS b
where a.ID = b.ID);
QUIT;

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
  • 784 views
  • 0 likes
  • 3 in conversation