DATA Step, Macro, Functions and more

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

Reply
N/A
Posts: 0

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

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!
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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.
N/A
Posts: 0

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

Posted in reply to deleted_user
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.
Frequent Contributor
Posts: 139

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

Posted in reply to deleted_user
Using the update statement in the data step in another option. The update is great b/c it only replaces non-missing values.
Ask a Question
Discussion stats
  • 3 replies
  • 165 views
  • 0 likes
  • 3 in conversation