topic Merge with (IN=A) in SAS Programming

Merge with (IN=A)

HeatherNewton — Wed, 23 Nov 2022 11:58:36 GMT

data seta;
input apple banana;
datalines;
1 1
2 2
3 3
run;

proc sort data=seta;
by apple;
run;

data setb;
input apple orange lemon pear;
datalines;
11 11 11 11
22 22 22 22
1  2 3 4 
run;

proc sort data=setb;
by apple;
run;

data outina;
merge seta (in=a) setb;
by apple;
run;

data outnoth;
merge seta setb;
by apple;
run;

proc print data=seta;
run;

proc print data=setb;
run;

proc print data=outina;
run;

proc print data=outnoth;
run;

I am getting exact same result for with or without (in=a), why?

I was believing (in=a) is like a left join... where only observations with apple =1,2,3 would be included...

Re: merge

PeterClemmensen — Wed, 23 Nov 2022 10:03:44 GMT

@HeatherNewton Good question.

The IN= Data Set Option "Creates a Boolean variable that indicates whether the data set contributed data to the current observation." (Straight from the documentation). So in your example, the in=a data set option creates a 0/1 variable (automatically dropped). You can assign the value to another variable to see it in your output like below.

data outina;
merge seta (in=a) setb;
by apple;
ina = a;
run;

Result:

apple banana orange lemon pear ina
1     1      2      3     4    1
2     2      .      .     .    1
3     3      .      .     .    1
11    .      11     11    11   0
22    .      22     22    22   0

Therefore, to replicate an SQL Left Join, you should use a Subsetting If Statement like below to output only the observations where the seta data set contributes.

data outina;
merge seta (in=a) setb;
by apple;
if a;
run;

Result:

apple banana orange lemon pear 
1     1      2      3     4    
2     2      .      .     .    
3     3      .      .     .

Re: Merge with (IN=A)

PeterClemmensen — Fri, 25 Nov 2022 08:57:42 GMT

@HeatherNewton , did this answer your question?

Re: Merge with (IN=A)

HeatherNewton — Fri, 25 Nov 2022 09:02:50 GMT

yes thx

Re: Merge with (IN=A)

PeterClemmensen — Fri, 25 Nov 2022 09:17:17 GMT

Good. Please remember to close the thread 🙂