topic Ratio or percent of another column (REPORT, TABULATE) in ODS and Base Reporting

Ratio or percent of another column (REPORT, TABULATE)

Davanden — Thu, 08 Dec 2016 15:27:52 GMT

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;

Rowvar	a	b	c	d	b	c	d
	sum	sum	sum	sum	pct?	pct?	pct?
Able	20	5	3	4	25.0	15.0	20.0
Baker	30	10	6	8	33.3	20.0	26.7
Charlie	40	15	9	12	37.5	22.5	30.0
Delta	50	20	12	16	40.0	24.0	32.0

--Dav

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

ballardw — Thu, 08 Dec 2016 15:40:16 GMT

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>

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

Davanden — Thu, 08 Dec 2016 15:58:22 GMT

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

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

ballardw — Thu, 08 Dec 2016 16:14:36 GMT

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.