Friday, March 25, 2011

Calculating Cumulative Percentage In PROC REPORT With ACROSS

I am finally able to get cumulative percentages calculated when /across is used in PROC REPORT.  Basically I have to programmatically calculate it in the code, and put the resulting value into the columns directly (such as _c4_ for column 4, _c7_ for column 7).  I contacted SAS technical support on this, and confirmed there was no way around this hard-coding of column number.  How sad!

Here is the SAS code, with column numbers hard-coded, but at least it works:
options missing=0;
proc report data = some_dataset nowd missing;
column credit_load snapshotdate,(ones ones=onespct cumpct) dummy;
define credit_load        / group 'Credit Load' ;
define snapshotdate     / across '' order=internal;
define ones                     / analysis sum 'Freq' style(column)={tagattr="format:##0"}    ;
define onespct               / analysis pctn 'Pct' style(column)={tagattr="format:##0.0%"};
define cumpct               / 'Cum Pct' style(column)={tagattr="format:##0.0%"};
define dummy              / noprint;

COMPUTE BEFORE ;
    cumtot1 = 0;
    cumtot2 = 0;
ENDCOMP;

COMPUTE dummy;
    cumtot1 + _c3_;
    cumtot2 + _c6_;

    _c4_ = cumtot1;
    _c7_ =  cumtot2;

    IF _BREAK_="_RBREAK_" THEN DO;
      _c4_=1;
      _c7_=1;
    END;
ENDCOMP;

rbreak after / summarize ;

run;

No comments: