Creating Sum variables with different sorting

[cmemtsa]

Hi to all! I'm running this proc sql as I want to create two new variables as sum of the same variable but with different grouping each time but I get error msg. Is if feasible under a single proc sql? proc sql; SELECT P, L, Exp, ((select sum(exp) FROM t group by P ) as TotalExp ), ((Select sum(exp) FROM t group by P,L ) as TotalExpL), FROM t; run;

[sustagens · Accepted solution]

You can do it by joining the same table in the "from" statement, but each table is grouped differently

proc sql;
select 
t1.P, 
t1.L, 
t1.Exp,
t2.TotalExp,
t1.TotalExpL 
from 
(select P, L, Exp, sum(Exp) as TotalExpL from t group by P, L) t1 
inner join 
(select P, sum(Exp) as TotalExp from t group by P) t2 
on t1.P=t2.P
;
quit;

[cmemtsa]

Thanks. It worked for this case. If becomes more complicated with one more variable for sorting (see below), how can we proceed? proc sql; select t1.P, t1.L, t1.Exp, t1.cd, t2.TotalExp, t1.TotalExpCd t1.TotalExpL from ((select P, L, Exp, sum(Exp) as TotalExpL from t group by P, L) t1 (select P, L, Exp, sum(Exp) as TotalExpCd from t group by P, CD)) t1 inner join (select P, sum(Exp) as TotalExp from t group by P) t2 on t1.P=t2.P ; quit;

[Kurt_Bremser]

Please use the "little running man" to post your code, and do some formatting on the code. This spaghetti is nigh unreadable.

[cmemtsa]

I don't know why it appears like that. In the preview the format is correct.

[cmemtsa]

Thanks. It worked for this case. If becomes more co

proc sql; 
select t1.P, 
t1.L, 
t1.Exp,
 t1.cd, 
t2.TotalExp,
 t1.TotalExpCd 
t1.TotalExpL 
from
 (((select P, L, Exp, sum(Exp) as TotalExpL from t group by P, L) t1 
(select P, L, Exp, sum(Exp) as TotalExpCd from t group by P, CD)) t1)
 inner join (select P, sum(Exp) as TotalExp from t group by P) t2 
on t1.P=t2.P ; 
quit;

mplicated with one more variable for sorting (see below), how can we proceed?

[Kurt_Bremser]

Your ERROR is caused by an unneeded and invalid comma, and probably a missing QUIT:

proc sql;
SELECT
 P, L, Exp,
 ((select sum(exp) FROM t group by P ) as TotalExp ),
 ((Select sum(exp) FROM t group by P,L ) as TotalExpL), /* this comma is not needed */
 FROM t; 
run; /* SQL statements are executed immediately, so no RUN is needed */
/* use a QUIT statement to end the SQL procedure */

[cmemtsa]

It does not work. proc sql; SELECT P, L, Exp, ((select sum(exp) FROM t group by P ) as TotalExp), ((Select sum(exp) FROM t group by P,L ) as TotalExpL) FROM t; quit;

[cmemtsa]

It does not work

proc sql;
SELECT
 P, L, Exp,
 ((select sum(exp) FROM t group by P ) as TotalExp),
 ((Select sum(exp) FROM t group by P,L ) as TotalExpL) 
 FROM t; 
quit;