topic Re: Updating a master data set from an update data set. in SAS Programming

Updating a master data set from an update data set.

deleted_user — Thu, 14 May 2009 09:20:27 GMT

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

Re: Updating a master data set from an update data set.

sbb — Thu, 14 May 2009 12:06:03 GMT

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

Re: Updating a master data set from an update data set.

deleted_user — Fri, 15 May 2009 08:38:22 GMT

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.

Re: Updating a master data set from an update data set.

sbb — Fri, 15 May 2009 10:30:17 GMT

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.

Re: Updating a master data set from an update data set.

deleted_user — Fri, 15 May 2009 10:40:31 GMT

Scott Barry, are you God?

Re: Updating a master data set from an update data set.

deleted_user — Fri, 15 May 2009 11:46:16 GMT

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!

Re: Updating a master data set from an update data set.

sbb — Fri, 15 May 2009 12:18:49 GMT

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.

Re: Updating a master data set from an update data set.

Florent — Wed, 20 May 2009 16:54:36 GMT

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;