LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (December 2001, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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

Scott, 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); modify lookup; index create zip; run;

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: data master; set master (in=a); set lookup key=zip; if a; run;

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.

Dan Fortuna (Scott Chupack) wrote in message news:<>... > 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 > > Result: > Scott 48335 MI > Bill 48335 MI > Mark 60031 IL > Mike 60031 IL

Back to: Top of message | Previous page | Main SAS-L page