BASE SAS: How to make subgroup pct in tabulate

[kimer]

I'm looking for a denominator syntax for subgroups total in proc tabulate. My "product" is a report with 2 across groups. Rowgroups are dynamic multible. Case with sashelp.class below. The problem is that subgroup total for F and M is not 100 but the same as rowpctsum. Other calculations er ok. Normaly solved in proc report, but report make a column for every combination (se age 11 and 15). To remove these we have to made both data step and macro loop in compute statement. Solution with a subgroup total in dataset with sql give a problem in the "all" statement.

Any idees?

data class;
set sashelp.class;
firstl = substr(name,1,1);
run;


proc tabulate data=class;
where firstl in ("J","A");
class firstl sex age;
var height;
table (all="Total" firstl="") * (height="SUM"*sum=""
								height="PCT totalrække"*rowpctsum=""
								height="PCT subgroup"*pctsum<sex*all  age all>=""
								), sex=" "  * (age="" all="I alt") all="Total" 
							/ box="Udfordring: PCT subgroup f*ialt=100% og m*ialt=100 ";
run;

proc report data=class nowd;
  title 'I report medtages alle kolonnekombinationer i _cn_ -> fjernes ved noprint og macro loop i compute';
  where firstl in ("J","A");
  column firstl n sex, age ;
  define firstl / group;
  define sex / across;
  define age / across ;
  rbreak after /summarize;
run;

[KristianLønø]

Hi.

I think this syntax will give you your subgroup percentages:

proc tabulate data=class;
where firstl in ("J","A");
class firstl sex age;
var height;
table (all="Total" firstl="") * (height="SUM"*sum=""
                                height="PCT totalrække"*rowpctsum=""
                                height="PCT subgroup"*pctsum<age all>=""
                                ), sex=" "  * (age="" all="I alt") all="Total"
                            / box="Udfordring: PCT subgroup f*ialt=100% og m*ialt=100 ";
run;

The clue is to use the subgroup variables for the denominator in the pctsum expression, which is "age all" in your example.

[kimer]

Thanks for the quick response. Works perfect.