Getting cartesian product in self join

harshpatel — Wed, 13 Sep 2023 05:40:14 GMT

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;

Re: Getting cartesian product in self join

Kurt_Bremser — Wed, 13 Sep 2023 06:41:55 GMT

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.

Re: Getting cartesian product in self join

harshpatel — Wed, 13 Sep 2023 07:39:57 GMT

Thanks it works

topic Getting cartesian product in self join in SAS Programming

Getting cartesian product in self join

Re: Getting cartesian product in self join

Re: Getting cartesian product in self join