topic Re: Sas table look up using set statements in SAS Programming

Sas table look up using set statements

sfffdg — Thu, 28 May 2020 06:42:21 GMT

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;

Re: Sas table look up using set statements

sfffdg — Thu, 28 May 2020 06:45:02 GMT

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

Re: Sas table look up using set statements

Kurt_Bremser — Thu, 28 May 2020 07:57:48 GMT

What is the rule for joining a and b? Why do you only take the "brown" for "sa"?

Re: Sas table look up using set statements

sfffdg — Thu, 28 May 2020 08:31:40 GMT

Hi Kurt,

The rule for joining and b is the key=name.

and the question is about the output , why I have got the last record in the result , which is below
SA 8 F brown More

when no matching records exist in table 'c' ( which is 'Brown' and not 'brown') , in that case the last record in the output should be
SA 8 F brown

Re: Sas table look up using set statements

sfffdg — Thu, 28 May 2020 08:44:44 GMT

The below is the final query , which output I concern about:

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;

Re: Sas table look up using set statements

Kurt_Bremser — Thu, 28 May 2020 09:15:37 GMT

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.

Re: Sas table look up using set statements

CarloPetti — Thu, 28 May 2020 10:16:53 GMT

If colr is not found in datset C, skinds take the last value founded. You need to manage this.

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;
skindis="";
_error_ = 0;
end;

run;

Re: Sas table look up using set statements

sfffdg — Thu, 28 May 2020 23:15:34 GMT

key=colr /unique---always takes the first observation , if we have multiple records matching the criteria

Re: Sas table look up using set statements

sfffdg — Thu, 28 May 2020 23:22:10 GMT

key=name /unique--always takes the first observation , if multiple records are matching the criteria.

Ok , to make it simple ,I have changed the dataset b to the below:

data b(index=(name));
input name $ colr $ ;
cards;
sa brown
Sb fair
AY fair
SA brown
Sy Fair
;
run;

Even now,the same output though no color matching criteria:

name age gender colr skindis
sa 33 F brown
Sb 39 M fair More
AY 11 M fair More
SA 8 F brown More

Re: Sas table look up using set statements

Tom — Fri, 29 May 2020 00:18:58 GMT

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