proc sql

[Pooja2]

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.

[Reeza]

What is your expected output based on the data shown?

[Pooja2]

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

[PGStats]

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;

[PGStats]

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;