proc tabulate percentage per column

[dcortell]

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

[dcortell · Accepted solution]

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;

[yabwon]

PCTSUM ?

[dcortell]

Hey buddy. It worked for me yes

[yabwon]

"PCTSUM ?" - meaning "why not PCTSUM ?" 😉

[dcortell]

ahahahah I got it 😉