Creating percentages in PROC REPORT

[Wouter]

Hi,

What I'm heading for, is an overview with monthly figures (given already by DB), along with 2 kinds of percentages: total, and per period. I can't get those percentages right (they appear as missing). As far as I can see, the code should work (but... it doesn't). Can anybody have a look what's wrong? Thanks in advance!

proc report data=SELECTIE2

nowindows missing headline headskip;

column PRODUCTION_MONTH bucket MONTH_ON_BOOK pct_state percent  ;

define PRODUCTION_MONTH / group;

define bucket / group;

define MONTH_ON_BOOK / n;

define pct_state / '% of Production Month';

define percent / '% of Total';

break after PRODUCTION_MONTH/ol summarize;

compute after PRODUCTION_MONTH;

  item=catt(PRODUCTION_MONTH,' Total');

  PRODUCTION_MONTH = '';

  line @1 ' ';

endcomp;

rbreak after / ol summarize;

compute after;

  involved = 'Grand Total';

endcomp;

run;

[Cynthia_sas]

Hi:

  I am confused by several things in your code.

1) You reference a variable called INVOLVED in your COMPUTE AFTER block, but I do not see a variable named INVOLVED in your COLUMN statement. Is INVOLVED a temporary variable? Where do you use it? What is the purpose of the COMPUTE AFTER?

2) Same thing with your assignment statement for ITEM. It's not in the COLUMN statement. It looks like the kind of subtotal string where you append the text 'Total' to the value of your GROUP variable. But I only see PRODUCTION_MONTH and BUCKET as GROUP items on the report. Why are you assigning a value to ITEM in one COMPUTE block and to INVOLVED in the other COMPUTE block?

3) PCT_STATE and PCT have no usage, therefore their default usage is the SUM statistic (if they are numeric?) or DISPLAY (if they are character?)

4) Did you "pre-summarize" and calculate the percents or did you want PROC REPORT to do the percents for you. Just naming a report item "percent" or "pct" won't make PROC REPORT calculate a percent. Especially if you don't use the PCTN statistic or compute one yourself.

5) You say that "as far as you can see the code should work" Has any code such as this worked in the past to correctly give you percents and percent of subgroup?

6) You did not provide any data in order for anyone to try to run code. Without data, looking at the program isn't going to help much.

7) Why do you have PRODUCTION_MONTH=' '; in the COMPUTE AFTER PRODUCTION_MONTH? The SUPPRESS option on the BREAK statement will suppress or blank out the value for that item on the break line.

😎 If you are NOT pre-summarizing and calculating for PCT_STATE and PCT, where are your PROC REPORT COMPUTE blocks to calculate those numbers?

9) You show the use of LISTING options like OL and HEADLINE/HEADSKIP -- these will be ignored by ODS destinations except for LISTING. What is your destination of choice for this report?

There have been previous forum postings on the calculating percents in PROC REPORT (such as this one https://communities.sas.com/message/149754#149754) with links to other papers that also discuss percentages. I would recommend that you start there.

cynthia

[Wouter]

No, you're right. I've used an old code which uses a table in which the percentages are already incorporated (in 2 columns). Since I'm facing the problem of creating the same outcome with another dataset right now (which has not the computed percentages in it I now see), I have to come up with another solution.

Since I'm dealing with work related data / queries, I have renamed the variables. But... not all of them supposedly. This explains the strange non-excisting contexts. Thanks for your extensive reply and your effort: I'm going to follow the link you've provided to get this going!

[Wouter]

Hi, thank you for your extensive reply! The query I've used, was used before on another dataset. In this dataset, the percentages were already incorporated (2 columns). Right now, I'm dealing with another dataset which has not any percentages in it (I see now). The strange non-excisting connections (variables) are coming from the changes I've made to the code (since I'm dealing with company data / queries) --> these are not very useful in this one... I'm sorry for the inconvenience!

I thank you for the link --> I'm going to change the query based on the new dataset which contains no extra info regarding %.

[Wouter]

Hi,

One question again! 🙂 I've created the query which deliveres the desired output as wanted, but...below the column named 'BUCKET' the number of xxx is vissible (made all unreadable -> company data, except for 2 figures to give you an idea of what is was I wanted. Do you know how I can rename this column (so BUCKET = nr. of xxx)? Thanks in advance for your answer!

I've used the following code:

proc report data=xxx nowd

out=outpct split='*';

column PRODUCTION_MONTH bucket Bucket=wtn percnt tpercnt;

define PRODUCTION_MONTH / group ;

define bucket / group format=buckets. 'Bucket*name' order=internal;

define MONTH_ON_BOOK / n;

define percnt / computed format=percent8.2

'Percent by *PRODUCTION_MONTH';

define tpercnt / computed format=percent8.2

'Percent*Total';

compute before;

totcount = wtn;

endcomp;

compute before PRODUCTION_MONTH;

count = wtn;

endcomp;

compute percnt;

percnt= wtn/count;

endcomp;

compute tpercnt;

tpercnt= wtn/totcount;

endcomp;

compute after PRODUCTION_MONTH;

percnt= wtn/count;

tpercnt= wtn/totcount;

endcomp;

break after PRODUCTION_MONTH / suppress summarize dol skip;

[Cynthia_sas]

Hi:

  It looks to me like BUCKET is a GROUP variable. You cannot ask for a GROUP variable to use a STATISTIC. And, I'm not sure what you mean when you say you want rename the BUCKET to show the number of XXX??? Do you know the number of XXX? Headers are put on the report before the data, so you already know how to rename a column header:

DEFINE varname / usage "The Label";

  Somehow, I think I am missing something about what you need to do. You say that the "number of XXX is vissible" (I assume on each row?) The other thing I don't understand is why you have BUCKET=WTN on your COLUMN statement. Generally, if WTN (the alias for BUCKET) is an aliase for BUCKET, you would also have a DEFINE statement for it. But if you intend for WTN to be a temporary variable, then it would not appear on the COLUMN statement. Can you explain? If you don't want to see BUCKET=WTN at all, you can use NOPRINT in the DEFINE. If you want to change the header of BUCKET=WTN, then you need a DEFINE statement for WTN.

  If your data is too confidential to post a sample of data, then your best bet for help is to work with Tech Support

  And, as I explained before, since you are using HTML or SASReport or PDF or RTF for your output, the options of HEADLINE, HEADSKIP, OL are ignored.

cynthia

[Wouter]

Hi,

Thanks for your reply. I've renamed the original datafile (yeah, my employer is quite strict...) to xxx, so you could see 'xxx' as a datasource. That's all.

The outcome of the file is exactly what I need, see the example (and the 'Percent by productionmonth of 201210 and the percenttotal, which are not 'blurred'). Only, the variable Bucket is mentioned twice (once as defined, as a text, but secondly as a number. I need this number indeed (a total of applications), but I can't change the header. Besides the part below, the variable Bucket is already defined so it can't be defined again.

So I need the colomn which is now called "BUCKET" (since I need these numbers), but preferably with another header name.

The file itself is also based on an example given by one of the white papers (how to present % in your Proc Report)...

[Cynthia_sas · Accepted solution]

HI:

  There are other ways to get counts and statistics. Aliases in PROC REPORT are very easy to use, once you understand that you have better luck with aliases and numeric variables to get different statistics. Once BUCKET is declared as GROUP, you cannot change the usage, so an alias for BUCKET doesn't make much sense. For example, in the code below, once I declare PRODTYPE to be GROUP, then the alias P2 can't be given any other usage. However, see the example below, using SASHELP.PRDSALE, it doesn't have percentages, but does make use of aliases and statistics to get counts and other statistics. Perhaps this will give you some ideas.

cynthia

ods listing close;

ods html file='c:\temp\prdsale.html' style=analysis;

proc report data=sashelp.prdsale nowd;

  where country='CANADA' and region='EAST';

  column country division prodtype prodtype=p2 n actual=cntrows actual actual=actmean;

  define country / group 'The country';

  define division / group 'The Division';

  define prodtype / group 'The product type';

  define p2 / group 'Alias = something else';

  define n / 'Use Statistic N for Count';

  define cntrows / n 'Get Count of Numeric variable' f=6.;

  define actual / sum 'Total Sales';

  define actmean / mean 'Avg Amt';

  rbreak after / summarize;

  compute after;

    country='Total';

    division='Group';

    prodtype='Group';

    p2 = 'Alias';

  endcomp;

run;

ods _all_ close;

[Wouter]

Thanks! I'll get on to it!