topic Re: PROC REPORT : SUMMARY LINE VALUE OF COMPUTED VARIABLE in ODS and Base Reporting

PROC REPORT : SUMMARY LINE VALUE OF COMPUTED VARIABLE

P_S_ — Tue, 09 Jul 2013 20:14:55 GMT

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;

Re: PROC REPORT : SUMMARY LINE VALUE OF COMPUTED VARIABLE

Cynthia_sas — Tue, 09 Jul 2013 21:47:32 GMT

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

Re: PROC REPORT : SUMMARY LINE VALUE OF COMPUTED VARIABLE

data_null__ — Tue, 09 Jul 2013 21:48:21 GMT

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