Merging Tables

[primukh26]

data inter1;
merge np_species_sorted(in=in1) np_codelookup_sorted(in=in2);
by ParkCode;
keep ParkCode Category ParkName;
if in1=1 and in2=1;
run;

data inter1;
merge np_species_sorted(in=in1) np_codelookup_sorted(in=in2);
by ParkCode;
keep ParkCode Category ParkName;
if in1=1;
run;

Can someone help me understand the difference between the two code snippets?

[PeterClemmensen · Accepted solution]

in the first code, you request only observations where both np_species_sorted and np_codelookup_sorted contribute with ParkCode. In SQL terms this is the same as an inner join. 

In the second code, you request observations where np_species_sorted contribute, but not necessarily np_codelookup_sorted. Consequently, you might see missing values for the variables that np_codelookup_sorted contribute with. This is a left join in SQl terms. 

Please ask if you want a deeper explanation 🙂

[primukh26]

Thanks for the explanation

[PeterClemmensen]

Anytime, glad to help 🙂

[Cynthia_sas]

Hi:

  To help you understand all the possibilities, this code illustrates how a SAS merge can generate multiple output tables with one pass through the data using IN= variables:

data mtable1;
  infile datalines;
  input commonvar amount;
  return;
  datalines;
11 115
13 315
14 415
16 615
;
run;

data mtable2;
  infile datalines;
  input commonvar name $ zip $;
return;
datalines;
10 Alicia 12345
11 Bryce 23456
12 Carolyn 34567
13 David 45678
14 Elsa 56789
15 Fern 67890
;
run;

data both oneonly twoonly allofone alloftwo;
  merge mtable1(in=inone) mtable2(in=intwo);
  by commonvar;
  if inone=1 then do;
     output allofone;
     if inone=1 and intwo=1 then output both;
     else if inone=1 and intwo=0 then output oneonly;
  end;
  if intwo=1 then do;
     output alloftwo;
     if intwo=1 and inone=0 then output twoonly;
  end;
run;

proc print data=both;
  title 'BOTH';
run;

proc print data=oneonly;
  title 'ONEONLY';
run;

proc print data=twoonly;
  title 'TWOONLY';
run;

proc print data=allofone;
  title 'ALLOFONE';
run;

proc print data=alloftwo;
  title 'ALLOFTWO';
run;

Hope this helps,

Cynthia