11-29-2013 03:16 AM
sasHi I have a question on SAS that I hope you may be able to help. Suppose that I have two SAS files called thismonth and lastmonth. They have the same fields except one of the fields.
Here is a snippet of the dataset
So what I need to do is to index the data by both contract number and code first. Then I want to do a lookup on lastmonth. For example. if last month's status is "N" then thismonth's status will be "I". And if last month's status is "R" then this month's status is "N". I'm not sure how indexing works in SAS and would appreciate any help. The real datasets have over 400000 rows in each of the dataset, so I think I have to use indexing in order for this lookup to go faster.
11-29-2013 01:24 PM
Doesn't look like a lookup problem to me. It looks like a straight forward merge.
data want ;
merge thismonth lastmonth(keep = contract code status rename=(status=oldstatus)) ;
by contact code ;
if oldstatus = 'N' then status='I' ;
else if oldstatus = 'R' then status = 'N';
11-29-2013 08:04 PM
Creating an index is only worth it if you're going to use this index multiple times.
For what you need to do you can either use a data step merge as Tom shows, a hash lookup (loading "Lastmonth" into the hash table), or a PROC SQL left join.
What is the primary key in your data? One of the data lines you've posted occurs twice. Is this just a "typo" or is this how your real data looks like?
11-30-2013 05:05 AM
All depend on size when performance is getting more attention. All things like hasing and Proc SQL have been mentioned many times.
Using Set / Key
There are however still an other solutions:
Using set wiht table lookup as example 8. SAS(R) 9.3 Statements: Reference
The number of records 400.000 is not that big. If you have some variable like 10 of lengt 10 bytes (recordlength 100 bytes) is not bigger as 40Mb.
That is small in our era using of Gb's en Tb's. All data could be easily fit into memory.
Using point/set is more work but could be interesting on big-data approaches
This is based on the approach of using a SAS datastet having a big SAS-dataset needed to get updated with lookups in a smaller dataset knowing the obs number. http://www2.sas.com/proceedings/sugi30/061-30.pdf Yes, at that era proc sql, SAS/access was not evolved that far. We were start working with hard disks of 10Mb and 640Kb as internal memory. The big blue machines 16Mb internal with 1 Gb drives.
That is humor seeing those figures.
With your small data size of 400k records my advice would be go for the most simple to understand solution. When it performs sufficient you are ready