topic Re: Update column with subtotals in New SAS User

Update column with subtotals

cmemtsa — Tue, 16 Jun 2020 11:19:59 GMT

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;

Re: Update column with subtotals

AhmedAl_Attar — Tue, 16 Jun 2020 11:28:20 GMT

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;

Re: Update column with subtotals

Jagadishkatam — Tue, 16 Jun 2020 11:30:29 GMT

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;

Re: Update column with subtotals

ed_sas_member — Tue, 16 Jun 2020 11:32:25 GMT

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,

Re: Update column with subtotals

cmemtsa — Tue, 16 Jun 2020 11:36:37 GMT

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

Re: Update column with subtotals

cmemtsa — Tue, 16 Jun 2020 11:38:50 GMT

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

Re: Update column with subtotals

AhmedAl_Attar — Tue, 16 Jun 2020 11:53:50 GMT

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

Re: Update column with subtotals

cmemtsa — Tue, 16 Jun 2020 12:17:42 GMT

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.

Re: Update column with subtotals

Ksharp — Tue, 16 Jun 2020 13:04:55 GMT

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;

Re: Update column with subtotals

cmemtsa — Wed, 17 Jun 2020 05:31:19 GMT

Thanks! This is exactly what I wanted.