The SAS Output Delivery System and reporting techniques

Ratio or percent of another column (REPORT, TABULATE)

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Ratio or percent of another column (REPORT, TABULATE)

I want to make a report that shows the sums of four columns plus the ratios or percentages of three of the sums to the other sum.  Is it possible to do this in one TABULATE or REPORT step?  See the code for what I have so far.  What I want to add is the sums of b, c, and d as a percentage of the sum of a, for each row--the last three columns of the example table.

 

It's easy to do this in two steps, but I was wondering if I could do this in only one.

 

/*what I have so far */
PROC TABULATE DATA=STUFF;
     WEIGHT weight;
     CLASS rowvar;
     VAR a b c d;
     TABLE rowvar,
          (a b c d)*SUM
     ;
RUN;

 

Rowvarabcdbcd
 sumsumsumsumpct?pct?pct?
Able2053425.015.020.0
Baker30106833.320.026.7
Charlie401591237.522.530.0
Delta5020121640.024.032.0

 

--Dav


Accepted Solutions
Solution
‎12-08-2016 10:57 AM
Super User
Posts: 11,134

Re: Ratio or percent of another column (REPORT, TABULATE)

Without example data it is hard to test code but if I understatnd what you want this may work:

 

(a b c d)*SUM b*Pctsum<a> c*Pctsum<a> d*Pctsum<a>

 

View solution in original post


All Replies
Solution
‎12-08-2016 10:57 AM
Super User
Posts: 11,134

Re: Ratio or percent of another column (REPORT, TABULATE)

Without example data it is hard to test code but if I understatnd what you want this may work:

 

(a b c d)*SUM b*Pctsum<a> c*Pctsum<a> d*Pctsum<a>

 

Contributor
Posts: 40

Re: Ratio or percent of another column (REPORT, TABULATE)

Thank you.  I was hoping it was something simple.  i confess that I always have trouble thinking about how percentages work in PROC TABULATE.

 

--Dav

Super User
Posts: 11,134

Re: Ratio or percent of another column (REPORT, TABULATE)

For a special treat see what happens if you use COLPCTSUM or ROWPCTSUM instead of PCTSUM. HINT: SAVE your data and program code before executing the code. Unless they have fixed the compiler recently to check for that usage you may generate a SAS system crash.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 229 views
  • 0 likes
  • 2 in conversation