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

2025 SAS Hackathon: There is still time!

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!

Register Now

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
  • 1399 views
  • 1 like
  • 2 in conversation