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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.