|Date: ||Fri, 28 Dec 2001 06:05:41 -0800|
|Reply-To: ||Dan Fortuna <dan.fortuna@CAPITALONE.COM>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Dan Fortuna <dan.fortuna@CAPITALONE.COM>|
|Subject: ||Re: Using a lookup Table in SAS|
|Content-Type: ||text/plain; charset=ISO-8859-1|
The 'obvious' way that SAS can do this using a data step is by sorting
the masterfile -- in this case on zip code -- and merging it with the
lookup file which is also sorted by zip, bringing the State field into
the masterfile that way.
However there's another way that's often better, especially if the
masterfile is large, or you don't want to sort it. You can create an
index on the second data set, and SAS can use a data step to look up
the record in the second file.
First step is to create the index. Let's say your datasets are called
"Master" and "Lookup". Do this first:
proc datasets library=(yourlibname);
index create zip;
Now you have an index on the second file, "lookup". This index can now
serve as a lookup key to use in a data step.
Second, run a data step that reads in the masterfile and looks up the
related fields from the lookup file. This step will do it:
set master (in=a);
set lookup key=zip;
When this data step runs (and the master dataset does not need to be
sorted in any particular way for it to work) it will look up the
record in the lookup dataset by zip code, and add the rest of the
fields contained in the lookup file, namely state. I use this all the
time to avoid sorting really big files. It might be a little slower
than an merge, which is the tradeoff here. It's really useful in
regular processes like the one you describe.
FYI you can also create composite indexes with two or more fields -
check the sas documentation for details.
Let me know if it works for you.
firstname.lastname@example.org (Scott Chupack) wrote in message news:<email@example.com>...
> I am a relatively new user of SAS and I am trying to figure out how to
> use lookup tables in SAS. What I would like to do is have a master
> data set with a field and based on the results of that field I would
> bring in the corresponding entry in the second data set.
> For example:
> Data Set 1:
> Name Zip
> Scott 48335
> Bill 48335
> Mark 60031
> Mike 60045
> Data Set 2:
> Zip State
> 48335 MI
> 60031 IL
> 60045 IL
> 90046 CA
> Scott 48335 MI
> Bill 48335 MI
> Mark 60031 IL
> Mike 60031 IL