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;
... View more
