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 ?" 😉
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.