Subtotals with different Group By

[cmemtsa]

Hi!

I have this dataset and I want to create a new table with all the table columns plus three new columns with subtotals based on different Group by.

Sum (Exp) Group by P

Sum (Exp) Group by P,L

Sum (MV) Group by P, CD

Is that feasible within a single proc sql?

Thank you for your help.

data T;
input P $ L CD $ EXP MV;
Datalines;
A 1 A1 10 100
A 1 A1 15 115
B 1 B1 20 120
B 2 B2 25 125
C 1 C1 30 130
C 2 C1 35 135
C 3 C2 40 140
;

[PaigeMiller · Accepted solution]

I don't think a single call to SQL can do this, but PROC SUMMARY can. in fact, PROC SUMMARY gives you additional sums taht you didn't ask for.

proc summary data=t;
	class p l cd;
	types p p*l p*cd;
	var exp mv;
	output out=sums sum=sum_exp sum_mv;
run;

[cmemtsa]

The output has double the lines of data

[PaigeMiller]

---

@cmemtsa wrote:
The output has double the lines of data

---

I don't know what you mean by this. When I run the code, I don't see double of anything. Please show me.

[PaigeMiller]

I still don't see any duplicates.

[Ksharp]

data T;
input P $ L CD $ EXP MV;
Datalines;
A 1 A1 10 100
A 1 A1 15 115
B 1 B1 20 120
B 2 B2 25 125
C 1 C1 30 130
C 2 C1 35 135
C 3 C2 40 140
;
proc sql;
create table want as
select * ,
(select sum(exp) from t where p=a.p) as sum_P,
(select sum(exp) from t where p=a.p and l=a.l) as sum_P_L,
(select sum(mv) from t where p=a.p and cd=a.cd) as sum_P_cd

 from t as a;
quit;

[cmemtsa]

That worked! Thank you!