topic Re: summarize data and sum in SAS Programming

summarize data and sum

aguilar_john — Fri, 19 Jun 2020 12:25:54 GMT

Hi everyone,

I have a really easy question but somehow I dont get the right result. I have a table like this:

date	balance	flag
01.04.2020	10	0
01.04.2020	20	1
02.04.2020	10	0
02.04.2020	10	0
02.04.2020	10	1

And I want to summarize it, so that my result table looks like this:

	total	flag_0	flag_1
01.04.2020	30	10	20
02.04.2020	30	20	10

Could you help me with posting the correct SAS code,thank you for the help.

Re: summarize data and sum

PaigeMiller — Fri, 19 Jun 2020 12:27:14 GMT

proc summary data=have nway;
    class date flag;
    var balance;
    output out=want sum=;
run;

This gives you the sums. If you absolutely have to have it with flag_0 and flag_1 columns (why? it's usually not a good idea to make data wider) then PROC TRANSPOSE gets you there.

Re: summarize data and sum

novinosrin — Fri, 19 Jun 2020 12:32:57 GMT

The stat genius @PaigeMiller is right about design, however for your requirement I would prefer Proc SQL-


data have;
input date mmddyy10. 	balance	flag;
format date mmddyy10.;
cards;
01.04.2020	10	0
01.04.2020	20	1
02.04.2020	10	0
02.04.2020	10	0
02.04.2020	10	1
;

proc sql;
create table want as
select date, sum(balance) as total,sum((flag=0)*balance) as flag0,sum((flag=1)*balance) as flag1
from have
group by date;
quit;

Re: summarize data and sum

aguilar_john — Fri, 19 Jun 2020 12:33:39 GMT

Thanks a lot!!
Also the PROC TRANSPOSE did work out. That is just the requested format...

Re: summarize data and sum

PaigeMiller — Fri, 19 Jun 2020 12:35:57 GMT

@novinosrin wrote:

The stat genius @PaigeMiller is right about design, however for your requirement I would prefer Proc SQL-


data have;
input date mmddyy10. 	balance	flag;
format date mmddyy10.;
cards;
01.04.2020	10	0
01.04.2020	20	1
02.04.2020	10	0
02.04.2020	10	0
02.04.2020	10	1
;

proc sql;
create table want as
select date, sum(balance) as total,sum((flag=0)*balance) as flag0,sum((flag=1)*balance) as flag1
from have
group by date;
quit;

Let me explain why I prefer PROC SUMMARY over PROC SQL.

If you have lots of values of FLAG (instead of just two), the PROC SQL solution is a lot more typing. The PROC SUMMARY/PROC TRANSPOSE solution requires no additional typing, it works for any number of values of FLAG.

Re: summarize data and sum

novinosrin — Fri, 19 Jun 2020 12:39:24 GMT

I fully agree. I did think of it, however since I noticed Flag variable is "binary" , I was pretty confident there to suggest the SQL approach. That being said, the two step solution is certainly the one that will scale for any number of ID value suffix.

Re: summarize data and sum

aguilar_john — Fri, 19 Jun 2020 13:50:25 GMT

Many Thanks! that is also a nice way to do it