BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
dcortell
Pyrite | Level 9

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

 

15.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
dcortell
Pyrite | Level 9

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;

View solution in original post

5 REPLIES 5
dcortell
Pyrite | Level 9

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
Amethyst | Level 16

PCTSUM ?

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



dcortell
Pyrite | Level 9
Hey buddy. It worked for me yes
yabwon
Amethyst | Level 16

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



dcortell
Pyrite | Level 9
ahahahah I got it 😉

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1457 views
  • 1 like
  • 2 in conversation