BookmarkSubscribeRSS Feed
meriS
Fluorite | Level 6

Hello all,

I would like to check if the ID numbers listed in one dataset are valid given a column of valid ID values in another dataset. 

For example:

Check: 

ID Name Age

A5 Liz 25

3B Bob 31

55 Bill 33

BE Ash 19

 

Valid: (this is an imported Excel file)

ID

A5

BE

18

3B

 

 

Desired output:

Discrepancies

ID Name Age

55 Bill 33

 

 

 

There is no set format for the ID (could be a string, integer, mixture) etc. Essentially, I want to cross reference each ID in Check with the entire column ID in Valid and keep the row if it is not found within Valid. 

 

I have tried:

 

data match discrepancy;

set check;

if ID in valid then output match;

else output discrepancy;

run;

 

which returns an error that the right hand operator is not an array name or constant value list. 

Any guidance would be appreciated. 

 
 
 
 
2 REPLIES 2
Patrick
Opal | Level 21

1. Sort both tables by ID, the lookup table with valid IDs using option nodupkey

2. Data step merge. Something like:

data valid invalid;
  merge have valid_ids(in=in_valid);
  by id;
  if in_valid =1 then output valid;
  else if in_valid=0 then output invalid;
run; 

 

Kurt_Bremser
Super User

Best approach with modern SAS tools, requires no sorting:

data
  match
  discrepancy
;
set check;
if _n_ = 1
then do;
  declare hash v (dataset:"valid");
  v.definekey("id");
  v.definedone();
end;
if v.check() = 0
then output match;
else output discrepancy;
run;

Untested; for tested code, provide usable example data in data steps with datalines.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 2 replies
  • 1206 views
  • 1 like
  • 3 in conversation