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

 

Dear Community,

 

I have 2 datasets:  dataset A and dataset B.

 

Dataset A contains several observations with unique identifier KEY, STATUS, name, address, etc...

 

Dataset B contains only 1 variable KEY.

 

What I would like to do:  for each observation in A, I want to check if the value for KEY occurs in dataset B, and if so, STATUS in dataset A has to change. (let's say status = 'Z')

 

I can manage that with SQL, by which I first create a new table based on a join of the 2 tables.

 

But I would like to know if there is a way to manage this easily in a datastep.

I would expect something like this:

 

data table_A_out;

set table_A_in;

 

if KEY in ( table_B.KEY) then STATUS = 'Z';

 

run;

 

But it doesn't work that way...

Any advise would be appreciated.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Hash Table

 

data IFILE ;
input  STATUS $       SEND_CNTY $;
cards;
T123                    EH
T456                    ZM 
T678                     US
T444                     ZW
;
run;
data CNTY_LOOKUP ;
input  CNTY_CODE2 $;
cards;
 EH
  YE
 ZM
 ZW
;
run;
data want;
 if _n_=1 then do;
  if 0 then set CNTY_LOOKUP;
  declare hash h(dataset:'CNTY_LOOKUP');
  h.definekey('CNTY_CODE2');
  h.definedone();
 end;
set IFILE;
if h.check(key:SEND_CNTY)=0 then STATUS ='Z';

run;

View solution in original post

3 REPLIES 3
Ksharp
Super User

Hash Table

 

data IFILE ;
input  STATUS $       SEND_CNTY $;
cards;
T123                    EH
T456                    ZM 
T678                     US
T444                     ZW
;
run;
data CNTY_LOOKUP ;
input  CNTY_CODE2 $;
cards;
 EH
  YE
 ZM
 ZW
;
run;
data want;
 if _n_=1 then do;
  if 0 then set CNTY_LOOKUP;
  declare hash h(dataset:'CNTY_LOOKUP');
  h.definekey('CNTY_CODE2');
  h.definedone();
 end;
set IFILE;
if h.check(key:SEND_CNTY)=0 then STATUS ='Z';

run;
fre
Quartz | Level 8 fre
Quartz | Level 8

Thank you for this very compact code.  It is what I was looking for.

 

One thing I don't understand:  the second if:  

if 0 then ...

 

If what exactly is zero?  And what does it means in this context?

Ksharp
Super User

It is creating(initiate) variables in PDV  which are in CNTY_LOOKUP table. 

Another way is  hard code Like :

 

length CNTY_CODE2  $ 8 

 

instead of 

if 0 then set ....

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 3568 views
  • 3 likes
  • 2 in conversation