```Date: Fri, 9 Nov 2007 19:42:31 -0500 Reply-To: "Howard Schreier " Sender: "SAS(r) Discussion" From: "Howard Schreier " Subject: Re: Percentages. Somehow this should be simple. Help! On Fri, 9 Nov 2007 15:50:55 -0800, Ali G wrote: >I want to create a tabular report. Across the top are weeks, and >under each week are three columns; previewed, booked and then a >percentage which is derived as booked/previewed. > >The rows are region and branch, and I'd like to provide summary totals >for each region and overall totals too. The subtotals and totals will >simply be the sum of previewed and booked. And I'd like to calculate >percentages for totals and subtotals also. > >It seems to me that I cannot render this report using proc tabulate, >summary, means or report. I seem faced with having to calculate the >percentages prior to actually producing the report otherwise the >report either gets the percentages wrong (for some rows) or cannot >calculate the percentages at all. To me SAS only seems to be able to >automatically calc the percentages for one variable compared to a >report total or subtotal; not one var compared to another. > >Am I missing something? The essence of the question is the percentages. Dragging all of the dimensions and nested subtotals into the problem statement was unnecessary and distracting. Nevertheless ... As Roy stated, an example is needed. Here is one: data demo; do week = 1,2; do branch = 'a1','b1','a2','b2'; region = substr(branch,1,1); prev = round(5*ranuni(123) ); book = round(prev*ranuni(123) ); if ranuni(123)<0.7 then output; end; end; run; DEMO looks like this: week branch region prev book 1 a1 a 4 1 1 b1 b 5 2 1 a2 a 4 2 1 b2 b 1 1 2 a2 a 1 1 2 b2 b 4 3 Here's a solution: options nocenter formchar="|-+++|+|+++"; proc tabulate data=demo; class region branch week; var prev book; table region * (branch all) all , (week all) * ( (prev book) * sum * f=4.0 book * pctsum * f=4.1 ) ; run; Result: +----------------------+------------------------+----+---------+----+ | | week |week| | | | |---------+----+---------+----| | | | | | 1 | | 2 | |All | | | 1 |----| 2 |----| All |----| | |----+----|book|----+----|book|----+----|book| | |prev|book|----|prev|book|----|prev|book|----| | |----+----|Pct-|----+----|Pct-|----+----|Pct-| | |Sum |Sum |Sum |Sum |Sum |Sum |Sum |Sum |Sum | |----------+-----------+----+----+----+----+----+----+----+----+----| |region |branch | | | | | | | | | | |----------+-----------| | | | | | | | | | |a |a1 | 4| 1|25.0| .| .| .| 4| 1|25.0| | |-----------+----+----+----+----+----+----+----+----+----| | |a2 | 4| 2|50.0| 1| 1| 100| 5| 3|60.0| | |-----------+----+----+----+----+----+----+----+----+----| | |All | 8| 3|37.5| 1| 1| 100| 9| 4|44.4| |----------+-----------+----+----+----+----+----+----+----+----+----| |b |branch | | | | | | | | | | | |-----------| | | | | | | | | | | |b1 | 5| 2|40.0| .| .| .| 5| 2|40.0| | |-----------+----+----+----+----+----+----+----+----+----| | |b2 | 1| 1| 100| 4| 3|75.0| 5| 4|80.0| | |-----------+----+----+----+----+----+----+----+----+----| | |All | 6| 3|50.0| 4| 3|75.0| 10| 6|60.0| |----------+-----------+----+----+----+----+----+----+----+----+----| |All | 14| 6|42.9| 5| 4|80.0| 19| 10|52.6| +----------------------+----+----+----+----+----+----+----+----+----+ The key is the denominator spec "" following the PCTSUM statistic. ```

Back to: Top of message | Previous page | Main SAS-L page