DATA Step, Macro, Functions and more

How to properly use indexes to do lookups in SAS

Occasional Contributor
Posts: 5

How to properly use indexes to do lookups in SAS

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


Contract NumberStatusPriceCode


Contract NumberStatusPriceCode

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.


Super User
Posts: 5,884

Re: How to properly use indexes to do lookups in SAS

This is a good starting point:

SAS(R) 9.4 Language Reference: Concepts

Data never sleeps
Super User
Super User
Posts: 8,125

Re: How to properly use indexes to do lookups in SAS

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


Respected Advisor
Posts: 4,736

Re: How to properly use indexes to do lookups in SAS

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?

Trusted Advisor
Posts: 3,215

Re: How to properly use indexes to do lookups in SAS

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.  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

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 4 replies
  • 5 in conversation