topic Re: Subtotals with different Group By in New SAS User

Subtotals with different Group By

cmemtsa — Thu, 11 Jun 2020 11:44:56 GMT

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
;

Re: Subtotals with different Group By

PaigeMiller — Thu, 11 Jun 2020 11:53:10 GMT

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;

Re: Subtotals with different Group By

cmemtsa — Thu, 11 Jun 2020 12:01:25 GMT

The output has double the lines of data

Re: Subtotals with different Group By

PaigeMiller — Thu, 11 Jun 2020 12:12:39 GMT

@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.

Re: Subtotals with different Group By

cmemtsa — Thu, 11 Jun 2020 12:20:19 GMT

Re: Subtotals with different Group By

Ksharp — Thu, 11 Jun 2020 12:25:42 GMT

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;

Re: Subtotals with different Group By

cmemtsa — Thu, 11 Jun 2020 12:30:52 GMT

That worked! Thank you!

Re: Subtotals with different Group By

PaigeMiller — Thu, 11 Jun 2020 13:56:03 GMT

I still don't see any duplicates.