PROC REPORT : SUMMARY LINE VALUE OF COMPUTED VARIABLE

[P_S_]

Hello All,

I need some help with the computed variable value in the summary line of the proc report.
I have the dataset and the code below. In the code "PSIV" is a computed variable.
It is computing the PSI for each of the "Family" code. In the summary line, however, I want the PSIV to summarize
the values in the column instead of using the computed logic. Is it possible to do that?

Please note that percnt_a and percnt_b are also computed variable in my original dataset. I want to get the desired output without having to change
the structure of the dataset. I appreciate your help.

Thank you.

data test;

input family $ loans_a balance_a  loans_b balance_b  ;

datalines;

CLASS_A 17 1060290.87 35 1917164.36

CLASS_B 1 64080.76  7 395020.65

CLASS_C 82 5532596.89 58 3300145.86

;

run;

ods tagsets.excelxp file='~/test1.xls' style=minimal;

proc report data=work.test nowd center missing

     style(header)=[background=CXDBE5F1] out=abc;

column  family loans_a balance_a avg_bal_a percnt_a loans_b balance_b avg_bal_b percnt_b psiv;

define  family        / group     format= $8.       "FAMILY" ;

define  loans_a       / sum       format= best9.      "POOL 1 LOANS" ;          

define  balance_a     / sum       format= best9.      "POOL 1 BALANCE"   ;       

define  avg_bal_a     / computed  format= best20.     "POOL 1 AVG BALANCE" ;     

define  percnt_a      / computed  format= percent10.3 "POOL 1 BAL (%) OF TOTAL" ;

define  loans_b       / sum       format= best9.      "POOL 2 LOANS"  ;          

define  balance_b     / sum       format= best9.      "POOL 2 BALANCE"   ;       

define  avg_bal_b     / computed  format= best20.     "POOL 2 AVG BALANCE" ;     

define  percnt_b      / computed  format= percent10.3 "POOL 2 BAL (%) OF TOTAL"  ;

define  psiv          / computed   "PSI" ;

compute before;

total_bal_a = _c3_;

total_bal_b = _c7_;

endcomp;

compute avg_bal_a;

avg_bal_a = _c3_/_c2_;

endcomp;

compute avg_bal_b;

avg_bal_b = _c7_/_c6_;

endcomp;

compute percnt_a;

percnt_a = _c3_/total_bal_a;

endcomp;

compute percnt_b;

percnt_b = _c7_/total_bal_b;

endcomp;

compute psiv;

psiv = (_c9_-_c5_)*log(_c9_/_c5_);

endcomp;

rbreak after /

   summarize

   style(summary) = Header{font_weight = bold

                    font_face   = 'Arial, Helvetica, Sans-Serif'

                    foreground  = black};

compute after;

  family = 'TOTAL';

endcomp;

run;

ods tagsets.excelxp close;

[Cynthia_sas · Accepted solution]

Hi, in your compute block, you would need to capture the PSIV value and create your own "summary" value. If you did something like this in your compute block:

compute psiv;

  psiv = (your calculation);

  holdtot + psiv;

endcomp;

then you would be creating a temporary variable that would "hold" the accumulating total of all the PSIV values. This bypasses repeating your formula. Then, you would also have logic to "swap" the HOLDTOT value for the PSIV value on the break. So the final COMPUTE block would look something like this:

compute psiv;

  psiv = (your calculation);

  holdtot + psiv;

  if _break_ = '_RBREAK_' then psiv = holdtot;

endcomp;

  I suggest you read the documentation topic entitled "How PROC REPORT Builds a Report" for more information about altering or changing values at a break. Here's an arbitrary example:

ods html file='c:\temp\somethingfake.html';

proc report data=sashelp.class nowd;

  column age sex ('Average' weight height);

  define age /group;

  define sex /group;

  define weight / mean;

  define height / mean;

  rbreak after / summarize;

  compute height;

    if _break_ = '_RBREAK_' then do;

       height.mean = 999;

       weight.mean = 888;

    end;

  endcomp;

  compute after;

    line 'Values for Height.Mean and Weight.Mean are arbitrary values';

  endcomp;

run;

ods _all_ close;

cynthia

[data_null__]

compute psiv;
psiv = (_c9_-_c5_)*log(_c9_/_c5_);
psivt + psiv;
endcomp;
compute after;
  family = 'TOTAL';
psiv=psivt;
endcomp;