Date: Fri, 14 Aug 1998 21:00:06 +0100
Reply-To: Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Subject: Re: Trouble with percentages in TABULATE
In-Reply-To: <903075660.2127988.0@vm121.akh-wien.ac.at>
In article <903075660.2127988.0@vm121.akh-wien.ac.at>, Pedro Perez
<tetsuo_4@HOTMAIL.COM> writes
>Dear SAS-L:
>
>I'm working with PROC TABULATE, and I must build a table with
>percentages, but I have a lot of problems with the denominator:
>I don't get the table that I want.
>I have two character variables, VARCHAR1 and VARCHAR2, and two
>numeric variables, VARNUM1 and VARNUM2.
>I want to obtain a table like this:
>
> VARCHAR2 ALL
> Value1 Value2 Value3
> Varnum1 varnum2 varnum1 varnum2 varnum1 varnum2 varnum1 varnum2
>VARCHAR1
>value1
>value2
>value3
>...
>...
>...
>
>I want ALL the percentages in VARNUM1 and VARNUM2 to sum 100 in EACH
>value of VARCHAR1 (each row of the table).
>I want the two numeric values in the column ALL, to sum 100,
>in each row of the table, too.
>
>My table is already built, I only need the expression I must put
>between the brackets (the denominator of the percentage).
>
>Thank you very much in advance.
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
I'm hoping to find a really clear posting that will explain this, but
can only offer my own ramblings ... so far
Building a denominator definition, it's important to understand that the
tabulate engine considers the definition:
must allow more than one denominator to be defined;
have an order of precedence to support these multiple definitions;
support both class vars as well as analysis vars.
as well as everything else I've forgotten...
The main learning point is that (surprise, surprise) precedence is from
left to right.
So, my guess at the denominator definition (needs a table defn first)
table varchar1, varchar2*pctsum< **guess1** >=' '
all*pctsum< **guess2** >=' '
/ rts = 20;
**guess1**
varchar2
**guess2**
varchar1
Seems a bit too simple
So, adding ALLs
table ( varchar1 all ), ( varchar2 all ) * ( varnum1 varnum2 )
* pctsum< **guess3** >=' '
/ rts = 20;
**guess3**
varchar2 varchar1 all
May need explanation
the components of the table for which varchar2 doesn't apply
(under the ALL column) won't use varchar2 from the denom def. and
so will use the total across all values of varchar1;
and again, on the all of (varchar1 all) i.e. the total row at the
bottom, the varchar2 analysis still applies even though it is an
ALL row;
only at the ALL column total of varnum1 and 2, where neither
varchar1 nor 2 apply, does ALL in the denom. def. signal 100%
Crawford Software Consultancy 20:40 Friday, August 14, 1998 1
testing denominator definitions totals
------------------------------------------------------------------
| | Number of bathrooms | |
| |-----------------------------------------| |
| | 1 | 2 | 3 | ALL |
| |-------------+-------------+-------------+-------------|
| |. beds|.sq.ft|. beds|.sq.ft|. beds|.sq.ft|. beds|.sq.ft|
|--------+------+------+------+------+------+------+------+------|
|Style of| | | | | | | | |
|homes | | | | | | | | |
|CONDO | .| .| 4| 3260| 7| 3495| 11| 6755|
|RANCH | 3| 1970| .| .| 6| 3035| 9| 5005|
|SPLIT | 1| 1190| 3| 1305| 4| 1615| 8| 4110|
|TWOSTORY| 4| 2280| .| .| 8| 3555| 12| 5835|
|ALL | 8| 5440| 7| 4565| 25| 11700| 40| 21705|
------------------------------------------------------------------
Crawford Software Consultancy 20:40 Friday, August 14, 1998 2
testing denominator definitions %
------------------------------------------------------------------
| sasuser| Number of bathrooms | |
|houses |-----------------------------------------| |
|% | 1 | 2 | 3 | ALL |
| |-------------+-------------+-------------+-------------|
| |. beds|.sq.ft|. beds|.sq.ft|. beds|.sq.ft|. beds|.sq.ft|
|--------+------+------+------+------+------+------+------+------|
|Style of| | | | | | | | |
|homes | | | | | | | | |
|CONDO | .| .| 36.4| 48.3| 63.6| 51.7| 27.5| 31.1|
|RANCH | 33.3| 39.4| .| .| 66.7| 60.6| 22.5| 23.1|
|SPLIT | 12.5| 29.0| 37.5| 31.8| 50.0| 39.3| 20.0| 18.9|
|TWOSTORY| 33.3| 39.1| .| .| 66.7| 60.9| 30.0| 26.9|
|ALL | 20.0| 25.1| 17.5| 21.0| 62.5| 53.9| 100.0| 100.0|
------------------------------------------------------------------
But my first guess wasn't right....
74 option ls=71 ps=80 nocenter pageno =1 ;
75 title2 'testing denominator definitions';
76 proc tabulate data=sasuser.houses format=6.1 missing noseps
77 formchar ='|----|+|---';
78
79 class style baths;
80 var bedrooms sqfeet;
81 table (style all), ( baths all)
82 *( bedrooms='. beds' sqfeet='.sq.ft')
83 *sum=' '*f=6.0 /rts=10;
84 table (style all), ( baths all)
85 *( bedrooms='. beds' sqfeet='.sq.ft')
86 *pctsum< baths style all > =' '
87 /rts=10 box= ' sasuser houses %';
88 run;
NOTE: The PROCEDURE TABULATE used 0.44 seconds.
--
Peter Crawford
