Create new variable given information available in multiple datasets

[camfarrell25]

Hello!

Quick question - is there an "easy" or "correct" way to go about with the following procedure:

I have two datasets:

1. dataset 1 is the dataset I principally work with

2. dataset 2 contains information necessary to modify dataset 1

Both dataset have a ID variable in common (Account #) that is unique to each observation.

I need to perform an IF/THEN/ELSE statement in dataset 1 to a subset of accounts that are identified as "Deficit", which is only available through information provided in dataset 2. Is it possible to create a binary variable in dataset 1 for accounts that are identified as "deficit" in dataset 2?

Would a merge be the only alternative?

Thank you in advance for your help,

CF

[jklaverstijn · Accepted solution]

Hi CF,

This has all the signs of a classic table lookup problem. This class of challenges can be approached in a few ways, a merge (SQL or Datastep) being amongst them.

Your final choice would be based upon aspects like the size of the base table, the lookup table and their relative sizes. Also your preparedness to recreate the base table is important. Without going to provide detailed code I would suggest, assuming that the base table is both large and much larger than the lookup table, is to used either:

1) SQL update

2) Datastep with MODIFY and a hash table lookup.

Keep in mind that every row in your table will have that binary (a datatype that does not exist in SAS; use a flag of type char(1)) variable. Just the value differs. Your question seems to suggest that you are looking for a way of adding the variable only for "Deficit" accounts.

Hope this helps you on your way. There are many many code examples on-line to get you going.

- Jan.