Hi!   I want to update a column with subtotals on another column based on grouping. Do I have to do an inner join in a subquery data T; input ID $ L M; Datalines; B 1 10 B 1 20 C 1 30 C 2 40 D 1 50 D 1 60 D 2 70 ; proc sql; alter table T add M1 numeric; update T set M1 = (select sum(M) from T group by ID, L ) ; quit; ? ... View more

Hi! I am trying to make calculations with lagged values within proc sql (I know function lag is not working) as this is only subpart of a longer proc sql program. Is it feasible? data T; input ID $ L M D ; Datalines; B 1 10 100 B 1 20 100 C 1 30 200 C 2 40 200 D 1 50 300 D 2 60 300 D 3 70 300 ; proc sql; Create table want as Select *, CASE WHEN L=1 THEN D-M WHEN L=2 then (SELECT D-LAG(M) FROM T WHERE ID=A.ID AND L=a.L) else (SELECT M-Lag(D) FROM T WHERE ID=A.ID AND L=a.L) END AS D1 FROM T AS A ; QUIT Output B 1 10 100 90 B 1 20 100 80 C 1 30 200 170 C 2 40 200 170 D 1 50 300 250 D 2 60 300 250 D 3 70 300 -230 ... View more

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

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

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