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 ?" 😉
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.