topic Joining a Tables in New SAS User

Joining a Tables

jaiganesh — Mon, 22 Apr 2019 21:04:47 GMT

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

Re: Joining a Tables

PeterClemmensen — Mon, 22 Apr 2019 21:11:23 GMT

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;

Re: Joining a Tables

jaiganesh — Mon, 22 Apr 2019 21:27:51 GMT

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.

Re: Joining a Tables

novinosrin — Mon, 22 Apr 2019 21:29:31 GMT

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;

Re: Joining a Tables

PeterClemmensen — Mon, 22 Apr 2019 21:32:40 GMT

@jaiganesh cool stuff 🙂 Glad you found your answer.