Joining a Tables

[jaiganesh]

Qu. Fetch the customer details who have shopped only in Jan.

Data Jan;
input Customer$ Item$ Amount;
Datalines;
A1 Sandal 110
A3 Watch 210
A5 Shoes 310
;
run;

Data Feb;
input Customer$ Item$ Amount;
datalines;
A1 Dresses 120
A2 Bags 220
A4 Paintings 320
;
run;

Output should be :

Final
Customer Item Amount
A3             Watch 210
A5             Shoes 310

Can any one please help me to write better SQL query.

Regards,

Jai

[PeterClemmensen · Accepted solution]

Data Jan;
input Customer$ Item$ Amount;
Datalines;
A1 Sandal 110
A3 Watch 210
A5 Shoes 310
;
run;


Data Feb;
input Customer$ Item$ Amount;
datalines;
A1 Dresses 120
A2 Bags 220
A4 Paintings 320
;
run;

proc sql;
   create table want as
   select * from Jan
   where Customer not in
   (select distinct Customer from Feb);
quit;

[jaiganesh]

Thanks for Help.

By the time i have also figure out below query.

proc sql;

select * from Jan where customer  not IN (select customer from Jan intersect select customer from Feb);

run;

Thank you.

[PeterClemmensen]

@jaiganesh cool stuff 🙂 Glad you found your answer.

[novinosrin]

Data Jan;
input Customer$ Item$ Amount;
Datalines;
A1 Sandal 110
A3 Watch 210
A5 Shoes 310
;
run;


Data Feb;
input Customer$ Item$ Amount;
datalines;
A1 Dresses 120
A2 Bags 220
A4 Paintings 320
;
run;

proc sql;
create table want as
select a.*
from jan a left join feb b
on a.customer = b.customer
where b.customer=' ';
quit;