summarize data and sum

[aguilar_john]

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.

[PaigeMiller · Accepted solution]

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.

[aguilar_john]

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

[novinosrin]

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;

[PaigeMiller]

---

@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.

[novinosrin]

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.

[aguilar_john]

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