topic Merging Tables in SAS Programming

Merging Tables

primukh26 — Tue, 13 Aug 2019 12:31:02 GMT

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?

Re: Merging Tables

PeterClemmensen — Tue, 13 Aug 2019 12:43:09 GMT

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 🙂

Re: Merging Tables

primukh26 — Sat, 17 Aug 2019 12:09:48 GMT

Thanks for the explanation

Re: Merging Tables

PeterClemmensen — Sat, 17 Aug 2019 12:12:01 GMT

Anytime, glad to help 🙂

Re: Merging Tables

Cynthia_sas — Sat, 17 Aug 2019 13:33:33 GMT

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