BookmarkSubscribeRSS Feed
kimer
Calcite | Level 5

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;
2 REPLIES 2
KristianLønø
Fluorite | Level 6

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
Calcite | Level 5

Thanks for the quick response. Works perfect.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 2 replies
  • 1088 views
  • 2 likes
  • 2 in conversation