Percentages in Proc report

[robertrao]

Hi Team,

I have a variable called "number" and it is divided into various groups. down below I have shown for 2 groups.

i need to take the sum of each group and get the percent of the total for each individual row.

in proc report if i write :

compute perc of total;

perc of total=number/number.sum;

endcomp;

will that be OK?

and also to get the subtotals(after each group)  is it like:

perc of total.sum=???

number       perc of total

50                  12.99

60                  15.58

75                  19.48

46                  11.95

55                  14.29

77                  20.00

22                  5.71

-------         ---------------

385              100                      subtotal

-------         -------------

25                14.29

55               31.43

36               20.57

59               33.71

--------        -----------

175            100                   subtotal

------         ------------

[Reeza]

Did it give you an error when you tried it? You can try things and if they don't work its easy to delete .  If you have to line up with cards that might be a different story...

You seem to try to do a lot of processing in proc report. As suggested last time its often more efficient to process the data outside of proc report and use proc report as a display mechanism instead.

[Cynthia_sas]

Hi:

  I think you have to be very, very specific about whether you want the percent of the GRAND total (across all subgroups) or the percent within the subgroup. When you show each subgroup adding up to 100%, then that is NOT the percent of total, but the percent of the subtotal for the subgroup (in my mind).

  I am not in a position to run code today, but look on page 3 of this paper (http://www2.sas.com/proceedings/forum2007/242-2007.pdf) and at the program that has produced this output. You will see the different way that percent of subgroup versus percent of grand total is calculated. This may actually be easier to accomplish with TABULATE, since you can use the <denominator> table operators to control the denominator for the percentage division. But REPORT can do it, as illustrated in the paper.

  I'd recommend looking at this paper -- note that it is billed as "ADVANCED" techniques -- so I would urge you to read some of the PROC REPORT documentation and some of the user-group papers to enance your basic understanding in order to move into advanced techniques with comfort. There is a great documentation topic entitled "How PROC REPORT Builds a Report" that deserves study.

cynthia

[Cynthia_sas · Accepted solution]

Hi:

  This type of report  is relatively simple to produce with PROC REPORT. But your example does not make sense. You are showing an already summarized report -- your desired output. But you do not give an example of your input data. You have given 2 different examples of what you want your output to look like. There should be a variable that is creating 2 different groups....you show 2 subtotals....therefore, PROC REPORT will expect you to have some variable on the report that tells it which observations fall into the first group and which observations fall into the second group. You could make the grouping variable a NOPRINT variable, but you don't give enough information to use your data for an example.

  Take a look at the code example below. It uses SASHELP.CLASS. Depending on the way groups are defined, you can see that the overall count of observations does not change (it is always 19), but the "breakdown" into groups is different. There are only 19 obs in SASHELP.CLASS and 5 variables. It should not be too hard to extrapolate from the way that SASHELP.CLASS looks to the way your data looks. Then, once you figure that out, you should be able to use of of these 3 reports as a model for what you want/need to do.

cynthia

ods listing close;
ods html file='c:\temp\use_pctn.html';

proc report data=sashelp.class nowd;
  title '1) Percents for Each Gender';
  column sex n pctn;
  define sex / group;
  define n / 'Count';
  define pctn / f=percent9.3;
  rbreak after / summarize;
run;

proc report data=sashelp.class nowd;
  title '2) Percents for Each Age';
  column age n pctn;
  define age / group;
  define n / 'Count';
  define pctn / f=percent9.3;
  rbreak after / summarize;
run;

proc report data=sashelp.class nowd;
  title '3) Show Percents by Gender and Age';
  column sex age n pctn;
  define sex / group;
  define age / group;
  define pctn / 'Percent of Grand Total' f=percent9.3;
  break after sex/summarize;
  rbreak after /summarize;
run;
ods html close;
title;

[New_Bee]

Thanks, Cynthia! I did not realize pctn statistics was available in proc REPORT - it does not seem to be documented, anyway (neither on-line nor in the paper manual). Is it possible to calculate multiple percentages without using compute blocks like in the shell below?

Age     Males     % Males     % Total     Females     % Females     % Total

[Cynthia_sas]

Hi:

  The list of statistics is here (documented under the interactive report designer):

Base SAS(R) 9.3 Procedures Guide, Second Edition

scroll down until you see STATISTIC=.

  If you look at this paper, it does show some examples of calculating % of total and then % of a group (see page 3):

http://www2.sas.com/proceedings/forum2007/242-2007.pdf

  But the structure of the data will make a difference in how easy it is to produce this type of report, given the headers that you show.

cynthia

[New_Bee]

Hi Cynthia!

The article seems to use compute blocks to calculate percentages rather than PCTN or PCTSUM statistics available in proc REPORT (I guess it's a new feature in 9.3). Can you provide an example of using PCTSUM?

Thanks again for your help!.

[Cynthia_sas]

Hi:

Do you want to use PROC REPORT or PROC TABULATE? PROC TABULATE is where you can use PCTSUM probably easier than with REPORT and can control the denominator. But REPORT does allow you to compute group percentages in a COMPUTE block.

I am in class today and not able to test code. Try this (untested, but should illustrate simple PCTSUM used in the HTP and WTP report items), if you wanted to add SEX into the report and then show the % of grand total and the % of age, you would have to use a COMPUTE block, as shown in the paper. Or switch to TABULATE and use the < and > operators to specify your denominator for the group.

cynthia

ods html file='c:\temp\showpctsum.html';

proc report data=sashelp.class nowd;

  column age height height=htp weight weight=wtp;

  define age / group;

  define height / sum;

  define htp / pctsum f=percent9.2;

  define weight / sum;

  define wtp / pctsum f=percent9.2;

  rbreak after / summarize;

run;

ods html close;