tabulate denominator question with A B AxB row variables

[juliarushing]

Hi.  I'm trying to produce some nested percents but can't seem to find the right denominator.  my original table statement looks something like this:

tables RACE AGEGROUP RACE x AGEGROUP, ALL*(n*f=8. colpctn*f=8.1);

all percents are correct until it comes to the RACE x AGEGROUP section.  I want the percents to add up to 100 across all agegroups within RACE, but of course SAS is providing percents that add up to 100 across the TOTAL N. I've tried various combinations of using PCTN and I can't fix it.

Any advice is appreciated!

thanks

Julia

[ballardw]

Please show entire procedure code.

If you want to use a different denominator for some parts of table you would either have to restructure you code so the statistics are associated with the row or possibly use a different table statement within the procedure.

You can specify a denominator for PCTN or PCTSUM using the syntax Pctn<var>  :(DO not use with any other percent statistics you may make SAS unstable). However since you are using three different sets of row variables you do not want to specify the same denominator for agegroup, race, and the combined race*agegroup. So the syntax gets a bit more complicated.

Here are a couple of approaches with a data set you can test code against showing something similar to your existing code and two different approaches to solutions. Note that your ALL as shown only actually has much affect in the last block.

proc tabulate data=sashelp.class;
   class sex age;
   /* your code equivalent*/
   table sex age sex*age,
         ALL*(n*f=8. colpctn*f=8.1)
    ;
   /* separate table for sex*age*/
   table sex age,
         ALL*(n*f=8. colpctn*f=8.1)
    ;
      /* this has the ages total 100pct within Sex*/
   table sex* age,
         ALL*(n*f=8. pctn<age>*f=8.1)
    ;
    /* or statistics in rows*/
    table (sex age)*  (n*f=8. colpctn*f=8.1) sex*age*(n*f=8. pctn<sex>*f=8.1),
          all
    ;
         

run; 

Interaction with more variables will likely make things more interesting.

[juliarushing]

Thanks. from what you've written it looks like I can't have the sex x age combination in the same tabulate as the (sex age) table for the percents to work properly. I will try that.

thanks again

Julia

[juliarushing]

yes, I see now that if I run the sex x age tabulation separately with pctn<age> the percents are correct but when this is included along with sex and age in the table the percents do not work, even though I am using

    tables sex age sex*age, all*(n pctn < sex age age>).  (does not calculate denom. correctly for sex x age)

tables sex*age,all*(n pctn<age>) does calculate denom correctly. weird.

guess if I'm determined to have all vars in the same table statement I'll have to replace the SEX  x AGE term with a newly derived SEXAGE var.

thanks again

Julia