Hi experts
i'm trying to get, at column level, the % represented by each cell, for each sub-group. code like this:
proc tabulate data=active_opp (where=(active_pipeline_&yy=1
and opp_class_cy="new_pipe" and quadrant in ("A"))) format=comma12.2
missing;
class
quadrant
account_industry
cy_impact
status_class ;
var sale_amount_usm;
table (quadrant)*account_industry*(cy_impact all='Grand Total' ),
(status_class all='Grand Total')*sale_amount_usm*
(sum="Total"*{style={background=pipe_vol.}}
pctn<cy_impact*sale_amount_usm
cy_impact*status_class
cy_impact*all
all
>)/rts=50
;
run;
However, the % at column levels do not reflect the proper percentage values, like below
Example: in column "open" the row "sourced" should be 76% but instead show a 56%. Any idea what I'm missing in the code?
Bests
I had to investigate more the pct statements but the following adjustments provided the proper % allocation in the table:
data targets ;
infile datalines delimiter=',';
input quadrant account_industry cy_impact sale_amount_usm status_class ;
datalines;
1, 11, 0, 11, 11
1, 11, 1, 22, 111
2, 22, 0, 22, 22
2, 22, 1, 33, 222
2, 22, 2, 22, 222
;
run;
proc tabulate data=targets format=comma12.2
missing;
class
quadrant
account_industry
cy_impact
status_class ;
var sale_amount_usm;
table (quadrant)*account_industry*(cy_impact all='Grand Total' ),
(status_class all='Grand Total')*sale_amount_usm *
(sum="Total"*{style={background=pipe_vol.}}
pctsum<
cy_impact*sale_amount_usm
cy_impact*all
all
>)/rts=50
;
run;
I had to investigate more the pct statements but the following adjustments provided the proper % allocation in the table:
data targets ;
infile datalines delimiter=',';
input quadrant account_industry cy_impact sale_amount_usm status_class ;
datalines;
1, 11, 0, 11, 11
1, 11, 1, 22, 111
2, 22, 0, 22, 22
2, 22, 1, 33, 222
2, 22, 2, 22, 222
;
run;
proc tabulate data=targets format=comma12.2
missing;
class
quadrant
account_industry
cy_impact
status_class ;
var sale_amount_usm;
table (quadrant)*account_industry*(cy_impact all='Grand Total' ),
(status_class all='Grand Total')*sale_amount_usm *
(sum="Total"*{style={background=pipe_vol.}}
pctsum<
cy_impact*sale_amount_usm
cy_impact*all
all
>)/rts=50
;
run;
PCTSUM ?
"PCTSUM ?" - meaning "why not PCTSUM ?" 😉
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.