topic Re: proc sql in SAS Programming

proc sql

Pooja2 — Tue, 28 Aug 2018 18:06:09 GMT

data tshirt;
input @1 id $5. @7 cloth $6. @14 receipt_number $3. @18 date mmddyy10.;
format date mmddyy10. ;
datalines;
12345 tshirt 22x 08/23/2018
12345 tshirt 23x 08/23/2018
12345 tshirt 24x 09/24/2018
12345 tshirt 25x 09/24/2018
12346 tshirt 22y 08/23/2018
12346 tshirt 23y 08/24/2018
12347 tshirt 25z 09/25/2018
;
run;

data others;
input @1 id $5. @7 cloth $6. @14 receipt_number $3. @18 date mmddyy10.;
format date mmddyy10. ;
datalines;
12345 perfum 23x 08/23/2018
12345 perfum 24x 08/23/2018
12347 perfum 25z 09/25/2018
12347 cologn 26z 09/25/2018
;
run;

*Question: who bought shirts and other stuff from 'others' data set on the same date?;

proc sql;
create table common as
select A.*, B.*
from tshirt A, others B
where A.ID = B.ID and A.date=B.date;
quit;

I am not getting what I want. Please help.

Re: proc sql

Reeza — Tue, 28 Aug 2018 18:07:11 GMT

What is your expected output based on the data shown?

Re: proc sql

PGStats — Tue, 28 Aug 2018 21:34:28 GMT

What you got is the cartesian product of the two subsets for each ID and date. You would want something like this:

proc sql;

/* Get the customer IDs */
create table common as
select unique A.ID, A.date
from 
    tshirt as A, 
    others as B
where A.ID=B.ID and A.date=B.date;

/* Get what they bought */
create table buys as
select a.id, a.date, a.cloth, a.receipt_number 
from tshirt as a inner join common as b on a.id=b.id and a.date=b.date
union all
select a.id, a.date, a.cloth, a.receipt_number 
from others as a inner join common as b on a.id=b.id and a.date=b.date
order by id, date;

quit;

Re: proc sql

Pooja2 — Wed, 29 Aug 2018 03:12:15 GMT

I want one row for one ID and what that ID bought in separate columns. Also, there are only one option for cloth in tshirt data and three options for cloth in others dataset. if possible, something like this:

ID date cloth1 cloth2 cloth3

12345 08/23/2018 tshirt perfume

12347 09/25/2018 tshirt perfume cologne

Re: proc sql

PGStats — Wed, 29 Aug 2018 03:21:48 GMT

Here is one last try:

proc sql;

/* Get the customer IDs */
create table common as
select unique A.ID, A.date
from 
    tshirt as A, 
    others as B
where A.ID=B.ID and A.date=B.date;

/* Get what they bought */
create table buys as
select a.id, a.date, a.cloth 
from tshirt as a inner join common as b on a.id=b.id and a.date=b.date
union
select a.id, a.date, a.cloth
from others as a inner join common as b on a.id=b.id and a.date=b.date
order by id, date, cloth;
quit;

/* Transform list into a table */
proc transpose data=buys out=buysTable(drop=_name_)
prefix=cloth;
by id date;
var cloth;
run;