topic Re: proc tabulate percentage per column in SAS Programming

proc tabulate percentage per column

dcortell — Mon, 08 May 2023 10:29:36 GMT

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

Re: proc tabulate percentage per column

dcortell — Mon, 08 May 2023 11:24:34 GMT

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;

Re: proc tabulate percentage per column

yabwon — Mon, 08 May 2023 11:29:05 GMT

PCTSUM ?

Re: proc tabulate percentage per column

dcortell — Mon, 08 May 2023 11:32:36 GMT

Hey buddy. It worked for me yes

Re: proc tabulate percentage per column

yabwon — Mon, 08 May 2023 12:01:54 GMT

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

Re: proc tabulate percentage per column

dcortell — Mon, 08 May 2023 12:25:57 GMT

ahahahah I got it 😉