Instead of Partition By in SQL

cmemtsa — Thu, 11 Jun 2020 09:14:40 GMT

Hi to all!

I have to find sums in one table but based on different Grouping be each time (relevant Partition by in SQL). How can I do it?

In this code I want to find Sum(Exp) group by P, sum(Exp) group by P, L and sum(MV) group by P, Cd). How many inner joins I need?

proc sql;
select * 

from 
(((select P, L, Exp, MV, sum(Exp) as TotalExpPL,  from t group by P, L) t2
inner join 
(select P, sum(Exp) as TotalExpP from t group by P) t1
on t1.P=t2.P)
((select  P, Cd, sum(MV) as TotalMVPCD from t group by P, Cd) t3
inner join on t1.P=t3.P))
;
quit;

Re: Instead of Partition By in SQL

ed_sas_member — Thu, 11 Jun 2020 13:50:35 GMT

Hi @cmemtsa

Why not using a PROC MEANS instead?

You can then filter on the _type_ variable to get the desired results.

proc means data=t noprint;
	class p L Cd;
	var Exp MV;
	output out=want sum=/autoname;
run;

topic Re: Instead of Partition By in SQL in SAS Programming

Instead of Partition By in SQL

Re: Instead of Partition By in SQL