SAS - Using the Summarize function of proc report to total percents

Question

I have a table about several organizations budgets with information for the past two years, and I need to add columns for this year's info. So essentially, I have a table with the columns: Year 1 Budget / Year 2 Budget / Y2 Change ($) / Y2 Change (%) / Year 3 Budget / Y3 Change ($) / Y3 Change (%).

data test;
        input Y1 Y2 y2changedollar y2changeperc y3 y3changedollar y3changeperc;  
        datalines;
            3700925 4940398 1239473 0.335 8687418 3747020 0.758
            700930 686911 -14019 -0.020 672175 -14736 -0.021
            2135970 2134584 -1386 -0.001 2162260 27676 0.013
            2373620 2326148 -47472 -0.020 2245862 -80286 -0.035
            555373 557830 2457 0.004 548386 -9444 -0.017
            ;
    run;

I need to generate a report that contains this information as well as a summary line at the bottom that totals each column. The prior year's document uses the summarize command within proc report to do this, but I think there's a problem with that.

PROC REPORT data=test;
 COLUMN y1 y2 y2changedollar y2changeperc y3 y3changedollar y3changeperc;
 DEFINE y1 / ANALYSIS FORMAT=DOLLAR15.;
 DEFINE y2 / ANALYSIS FORMAT=DOLLAR15.;
 DEFINE y2changedollar / ANALYSIS '$ Change' FORMAT=DOLLAR15.;
 /*DEFINE y2changeperc / analysis '% Change' FORMAT=pctc.;*/
 DEFINE y3 / ANALYSIS FORMAT=DOLLAR15.;
 DEFINE y3changedollar / ANALYSIS '$ Change' FORMAT=DOLLAR15.;
 /*DEFINE y3changeperc / analysis '% Change' FORMAT=pctc.;*/
 RBREAK AFTER / SUMMARIZE STYLE=[background=grey font_weight=bold font_style=italic];
;
  
RUN;

What that gives me is something like this:

| Y1 | Y2 | $Change | % Change | Y3 | $ Change | % Change |

| $9,466,818 | $10,645,871 | $1,179,053 | 0.298 | $14,316,101 | $3,670,230 | 0.698 |

With the bottom line stylized the way specified (grey background, bold, italic).

So the issues I'm having with this are:

Why is the actual data not printing in the table as well? (Probably a very simple & silly mistake, but SAS is not my preferred software, so I don't know what I'm missing.)
The percent change seems inaccurate. I calculate the percent change as (Y2-Y1)/Y1, and so for the total percent change, I would need to do that with the sums of the other columns - (TY2 - TY1)/TY1 (right?). The percent change for that should be 0.124, not 0.298, if my math is correct.

1179053 / 9466818 = 0.1245 (Year1-2 Change)

3670230 / 10645871 = 0.3447 (Year2-3 Change)

I know that the numbers in the table are gotten by adding the columns, the same as the other columns. Is there a way to stop/change that so that the math is correct?
If I format the two percent change columns as percents in the report (the commented out lines), instead of getting 0.298 and 0.698 for the two total percents, I get 0 and 1 respectively. Does anyone know what's happening there? In the actual dataset that I'm working with, the value that I'm getting is neither the summation of the percent column nor the calculation of the total percent change, and I have no idea what math is leading to the number.

If anyone has any insights into any of these issues, I would greatly appreciate the help. Thanks!

Reeza Reeza · Accepted Answer · 2021-10-29T17:49:29

You set the role of your variables to analysis not display so only the summary is displayed. Change ANALYSIS to DISPLAY.
I think you'll need to use a custom calculation here and not rely on the automatic calculations. Unfortunately don't know more about this one.
You're using a custom format so hard to say exactly why, but if you use percent12.1 format it shows correctly for me. Please show the definition of your percentage format.

Is this closer to what you wanted to see?

    
PROC REPORT data=test;
 COLUMN y1 y2 y2changedollar y2changeperc y3 y3changedollar y3changeperc;
 DEFINE y1 / display FORMAT=DOLLAR15.;
 DEFINE y2 / display FORMAT=DOLLAR15.;
 DEFINE y2changedollar / display '$ Change' FORMAT=DOLLAR15.;
DEFINE y2changeperc / display '% Change' FORMAT=percent12.1;
 DEFINE y3 / display FORMAT=DOLLAR15.;
 DEFINE y3changedollar / display '$ Change' FORMAT=DOLLAR15.;
DEFINE y3changeperc / display '% Change' FORMAT=percent12.1;
 RBREAK AFTER / SUMMARIZE STYLE=[background=grey font_weight=bold font_style=italic];
;
  
RUN;

SAS - Using the Summarize function of proc report to total percents

1 Answers