data a;
input name $ age gender $;
cards;
sa 33 F
Sb 39 M
AY 11 M
SA 8 F
Ny Fair
;
run;
data b(index=(name));
input name $ colr $ ;
cards;
sa brown
sa fair
Sb fair
AY fair
SA brown
Sy Fair
;
run;
data c(index=(colr));
input colr $ skindis $ SD $;
cards;
Brown Medium MD
fair More MR
Black less LS
;
run;
data cb;
set a;
set b (keep= name colr) key=name /unique;
if _error_ = 1 then do;
_error_ = 0;
end;
set c (keep= colr skindis) key=colr /unique;
if _error_ = 1 then do;
_error_ = 0;
end;
run;
If I run the above query , the result I get is
name age gender colr skindis
sa 33 F brown
Sb 39 M fair More
AY 11 M fair More
SA 8 F brown More
Please help me in achieving the below result:
name age gender colr skindis
sa 33 F brown
Sb 39 M fair More
AY 11 M fair More
SA 8 F brown
What is the rule for joining a and b? Why do you only take the "brown" for "sa"?
You have not answered my question. Why do you take sa/brown over sa/fair when joining dataset a on name with dataset b?
I am NOT interested in code, I am interested in the rule.
The issue is that any variable that exists in an input dataset is automatically "retained" (that is why one-to-many merges work). So you need to re-set them to missing so they aren't carried over.
PS Your IF statements are not really doing anything useful.
So if you input looks like this:
data a;
input name $ age gender $ @@;
cards;
sa 33 F Sb 39 M AY 11 M SA 8 F Ny 44 F
;
data b(index=(name));
input name $ colr $ @@;
cards;
sa brown Sb fair AY fair SA brown Sy Fair
;
data c(index=(colr));
input colr $ skindis $ SD $ @@;
cards;
Brown Medium MD fair More MR Black less LS
;
And you write your lookup/merge step like this:
data cb_right;
set a;
set b (keep= name colr) key=name /unique;
set c (keep= colr skindis) key=colr /unique;
output;
call missing(of _all_);
_error_ = 0;
run;
Then you get what I think you wanted:
Obs name age gender colr skindis 1 sa 33 F brown 2 Sb 39 M fair More 3 AY 11 M fair More 4 SA 8 F brown 5 Ny 44 F
If you change Brown in table C to brown so that it matches the values in table B then you get:
Obs name age gender colr skindis 1 sa 33 F brown Medium 2 Sb 39 M fair More 3 AY 11 M fair More 4 SA 8 F brown Medium 5 Ny 44 F
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.