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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.