How to properly use indexes to do lookups in SAS

[shukuchi]

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

Thismonth:

Contract Number	Status	Price	Code
43211100		300	CA
19180907		500	IF
43121999		1200	CA
54331762		700	IF
54331762		700	IF

Lastmonth:

Contract Number	Status	Price	Code
17190444	N	600	CA
43211000	I	300	CA
19180907	I	500	IF
54331762	R	700	IF
54331762	R	700	IF

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.

Thanks.

[LinusH]

This is a good starting point:

SAS(R) 9.4 Language Reference: Concepts

[Tom]

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';

run;

[Patrick]

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.

Question:

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?

[jakarman]

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