Subqueries Operators SQL

 data plan_2008;
 input plancode $ year plan $ cust;
 cards;
 p101 2008 1min/2rs 450
 p201 2008 2min/3rs 560
 p207 2008 1min/1ps 660
 p345 2008 2min/2rs 640
 p420 2008 1min/80p 713
 ;
data plan_2009;
input plancode $ year plan $ cust;
cards;
p109 2009 1sec/2p 780
p207 2009 1min/1rs 640
p225 2009 1sec/1p 680
p420 2009 1min/80p 750
p431 2009 2sec/1p 790
;
data ae;
input pid visit $ aetype $;
cards;
103 visit1 eye dis
125 visit1 ear dis
145 visit1 eye dis
178 visit1 ear dis
198 visit1 rashes
103 visit2 ear dis
113 visit2 ear dis
125 visit2 eye dis
178 visit2 rashes
198 visit2 ear dis
103 visit3 rashes
113 visit3 eye dis
178 visit3 rashes
210 visit3 ear dis
;

/*row comparision between subqueries and operators*/
/*in all visits*/
proc sql;
select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
intersect
select pid from ae where visit='visit3';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
intersect
select pid from ae where visit='visit3');
quit;

/*1st visit*/
proc sql;
select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3');
quit;

/*1st & 2nd visit*/
proc sql;
select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit1'
intersect
select pid from ae where visit='visit2'
except
select pid from ae where visit='visit3');
quit;

/*3rd visit*/
proc sql;
select pid from ae where visit='visit3'
except
select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2';
quit;

proc sql;
select * from ae where pid in(select pid from ae where visit='visit3'
except
select pid from ae where visit='visit1'
except
select pid from ae where visit='visit2');
quit;