BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
camfarrell25
Quartz | Level 8

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jklaverstijn
Rhodochrosite | Level 12

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

1 REPLY 1
jklaverstijn
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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