topic Re: How to do a lookup and replace values from one dataset to another? in SAS Programming

How to do a lookup and replace values from one dataset to another?

deleted_user — Wed, 06 Aug 2008 22:57:53 GMT

Hi All

I am trying to do a "lookup" if you like into one dataset and replace the matching values in another dataset. EG

Dataset A has the following values

0100 23001
0101 23002

And dataset B has the following values

0100
0101

What i want to do is look into dataset A and grab the corresponding values - so in this case I want to replace 0100 with 23001 in Dataset B and likewise, 0101 will be replaced to 23002 by looking into Dataset A for the value.

I am on version 9 and on the mainframe.

Any help will be greatly appreaciated.

Thanks!

Re: How to do a lookup and replace values from one dataset to another?

sbb — Wed, 06 Aug 2008 23:11:44 GMT

You have some options depending on preference mostly:

1) DATA step: MERGE on common-sorted files.
2) PROC SQL: JOIN to combine tables on comon variable list.
3) PROC FORMAT and DATA step using a PUT function to perform the look-up.

Recommend some DOC review on the above, and likely, check the SAS Support website at http://support.sas.com/ for SAS coding samples and examples in SAS SUGI/SGF technical conference papers.

Another option is a http://Google.com/ advanced such as:

+lookup +example site:sas.com

Scott Barry
SBBWorks, Inc.

Re: How to do a lookup and replace values from one dataset to another?

deleted_user — Thu, 07 Aug 2008 03:28:39 GMT

Hi

Thanks for replying

I resolved this issue by using firstly a join (PROC SQL) on the two datasets. I had to do this twice because I wanted to update two columns in the dataset to be updated. Then i did a MERGE on the two VIEWS I created (via proc sql) - the merge was on a key field (serial number).

Cheers
Shelton.

Re: How to do a lookup and replace values from one dataset to another?

darrylovia — Thu, 07 Aug 2008 12:01:57 GMT

Using the update statement in the data step in another option. The update is great b/c it only replaces non-missing values.