Update column with subtotals

[cmemtsa]

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;
 

?

[AhmedAl_Attar · Accepted solution]

This might do the trick

proc sql;
	select id, l, m, sum(m) as m1
	from t
	group by id, l
	order by id, l
	;
quit;

[cmemtsa]

That works but as this is a part  of the code, is there a solution with Update?

[AhmedAl_Attar]

Try this

proc sql;
    /* Create Temporary aggregates table */
	create table sums as
	select id, l, sum(m) as m1
	from t
	group by id, l
	order by id, l;

    /* Alter the table */
	alter table T add M1 num;

    /* Update the t table */
	update t
	set m1 = (select s.m1 from sums s where t.id=s.id and t.l=s.l);

	/* Drop the temporary aggregates table */
	drop table sums;
quit;

Hope this helps,

Ahmed

[cmemtsa]

I also thought of that solution but I wanted to ask whether there was a shorter version through creation of mirror table and inner join within the subquery.

I will go with that solution since it's not feasible otherwise. Thanks.

[Jagadishkatam]

please try below code

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;
create table want as select ID, L, M, sum(M) as sumM from T  group by ID, L ;
quit;

[ed_sas_member]

Hi @cmemtsa 

You cannot update the T table by using an inner query that uses this table as it is already in use.

I would suggest that you create another table:

proc sql;
	create table T2 as
	select *, sum(M) as M1
	from T
	group by 
	ID, L;
quit;

Best,

[cmemtsa]

Thank you but is there a way to use Update with subquery and though a mirror table?

[Ksharp]

If you really want UPDATE .

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;
create table _t as select * from t;
alter table T add M1 numeric;
update T 
set M1 = 
(select sum(M) from _T  where id=t.id and l=t.l )
;
quit;

[cmemtsa]

Thanks! This is exactly what I wanted.