Getting cartesian product in self join

[harshpatel]

Hi,

When i am trying to execute below code i am getting cartesian product while using inner join but i dont want cartesian, i need 6 rows only as it is with previous quarter comparision,

I have sent output as well, but instead of 8 rows it should be 6 rows only

Please help me on this

data t;
input id repdate $10. amount;
datalines;
1 31DEC2020 2000
1 31DEC2020 5000
1 31DEC2021 3000
1 30SEP2020 1500
1 30SEP2021 3000
1 30SEP2020 6000
;

data t1 (drop=repdate);
set t;
currqtr=input(repdate, date9.);
format currqtr date9.;
run;

proc sql;
create table t2 as
select a.*,intnx("qtr",a.currqtr,-1,"e") as prevqtr format date9.,
coalesce (b.amount,0) as previoussale,
coalesce ((a.amount-b.amount)/b.amount,0) as growth format=percentn8.1
from t1 as a left join
t1 as b
on a.id = b.id and intnx("qtr",a.currqtr,-1,"e") = b.currqtr
order by id;
quit;

[Kurt_Bremser · Accepted solution]

You have multiple entries per id and quarter, which have to be consolidated first for the process to result in meaningful output.

First use PROC MEANS to calculate sums or averages, then do the compare.

[harshpatel]

Thanks it works