DATA Step, Macro, Functions and more

Create new variable given information available in multiple datasets

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Create new variable given information available in multiple datasets

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

 

 


Accepted Solutions
Solution
‎05-17-2016 10:04 AM
Super Contributor
Posts: 441

Re: Create new variable given information available in multiple datasets

Posted in reply to camfarrell25

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.

View solution in original post


All Replies
Solution
‎05-17-2016 10:04 AM
Super Contributor
Posts: 441

Re: Create new variable given information available in multiple datasets

Posted in reply to camfarrell25

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 180 views
  • 1 like
  • 2 in conversation