SAS data merge for existence

Question

I am new to sas, i have two datasets as following,

data datasetA;
    input a $1;
    datalines;
1
2
3
4
5
6
7
;
run;

data datasetB;
    input a $1;
    datalines;
1
3
5
7
;
run;

If a appears B, then my desired output should be

1 Y
2 N
3 Y
4 N
5 Y
6 N
7 Y

Answer 1

data _a;
format a 3.;
do i = 1 to 7;
a = i;
output;
end;
drop i;

data _b;
format b 3.;
do i = 1 to 7 by 2;
b = i;
output;
end;
drop i;
run;

data _c;
merge _a(in=_a) _b(in=_b rename=(b=a));
format St $2.;
if _a and _b then St = 'Y';
else St = 'N';
by a;
run;

I would suggest you to google SAS merge or SAS proc sql join for introduction to basic concepts.

Answer 2

This can be achived by in at least two ways:

• merge via the data step or
• left join with the proc sql.

This pdf compares pros and cons of merge versus sql in sas.

Since rbet showed you how to do this with the merge step, I'll show you how to do it with the proc sql.

proc sql;
  create table work.result as
    select t1.a, case when t2.a is not missing then 'Y' else 'N' end as exists
    from work.datasetA t1
     left join work.datasetB t2 on t1.a = t2.a order by t1.a;