how to generate the summary

[walterwang]

I have the below data set. I want to use proc summary to get the summary like that

sex	total
1	…
2	….
9	…
all	…

sex	age	total
1	1	408563
1	2	338258
1	3	311091
1	4	121462
1	5	102785
1	6	26971
1	7	8573
1	8	17051
1	9	20
2	1	3710
2	3	12209
2	4	12745
2	5	4205
2	6	584
2	7	15521
2	9	29
9	1	981
9	2	108
9	3	10
9	4	459
9	5	294
9	6	9
9	7	35
9	8	136
9	9	7901

[andreas_lds]

Why proc summary? Proc report seems to be more fitting to get the desired output.

[RW9 · Accepted solution]

This:

proc summary data=sashelp.class;
  class sex;
  var age;
  output out=want (rename=(age=age_sum) drop=_type_ _freq_) sum()=;
run;

Would get you some of the way, but then you need to get a bit more creative:

proc format;
  value $sex (multilabel)
    "M"="Male"
    "F"="Female"
    "M","F"="All";
run;

proc summary data=sashelp.class nway;
  class sex / mlf order=formatted;
  var age;
  format sex $sex.;
  output out=want (rename=(age=age_sum) drop=_type_ _freq_) sum()=;
run;

Will get you the summary unfortunately not in order, but you can fiddle around with the order= option with your data - you have not posted any test data in the form of a datastep and I am not here to type it in!

[Reeza]

Use PROC MEANS which will generate the total and overall summary. If this is for display, PROC TABULATE, MEANS, or FREQ are more appropriate.

proc means data=have noprint;
class sex; 
ways 0 1;
var total;
output out=want sum=Total;
run;

proc tabulate data=have;
class sex;
var total;
table (sex all), total='Total'*sum=''*f=12.;
run;

[Astounding]

All of this is possible.  In fact, all of this is easy.  But please tell us what you are trying to do.  What goes in the TOTAL column?  Do we ignore AGE and get the sum of the original TOTAL values?  Are we getting the average of the original TOTAL values instead of the sum?

The hardest part is writing a program when you don't know what you are trying to achieve.

[walterwang]

It is hard to understand proc summary for different output. thanks guys.