Good day I have created a report using PROC REPORT and ODS EXCEL. I have calculated fields using compute from variables that are already in my sas data output. The excel file that is created also adds in formuale on the computed columns as required. However, my clients want to be able to change the values that are in a computed column in accordance with their needs.. this results in the other computed columns not being updated accordingly. Within a normal excel file, the values would automatically update themselves if one value changes.. How can I fix this? Is there perhaps some option available to do this? My sample code is attached. Unfortunately I cannot provide any data due to the sensitivity thereof. /***Previous code to get the Balances datset is here***/
goptions device=actximg;
options missing='';
ods listing close;
title color=black bold height=16pt wrap u=2 "Inflow Schedule";
ods excel options (sheet_name="Inflow" );
PROC REPORT DATA=work.balances out=work.test missing headline headskip;
column N1 Name Current_Bal Val Proj_Nas_Otp_90 Proj_Nas_Otp_80 Prox_Rates_Tax Prox_Cost Proj_Loss_At_Nas90 Proj_Loss_At_Nas80
Proj_SIE_Otp_70 Proj_Loss_SIE Proj_Cr_Refund_NAS Proj_Cr_Refund_SIE;
DEFINE N1 / display style(column)=[cellwidth=2.5cm cellheight=28];
DEFINE Name / display style(column)=[cellwidth=8.7cm cellheight=28 tagattr='wrap:no'] left;
DEFINE Current_Bal / sum style(column)=[cellwidth=2.5cm cellheight=28 tagattr="wrap:no"] f=comma12.2 right;
DEFINE Val / sum style(column)=[cellwidth=2.5cm cellheight=28 tagattr='wrap:no'] f=comma12.2 right;
DEFINE Proj_Nas_Otp_90 / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:no'] f=comma12.2 right 'Proj NAS OTP@90%';
COMPUTE Proj_Nas_Otp_90;
Proj_Nas_Otp_90= Val.sum * 0.9;
ENDCOMP;
DEFINE Proj_Nas_Otp_80 / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:no'] f=comma12.2 right 'Proj NAS OTP@80%';
COMPUTE Proj_Nas_Otp_80;
Proj_Nas_Otp_80= Val.sum * 0.8;
ENDCOMP;
DEFINE Proj_SIE_Otp_70 / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:no'] f=comma12.2 right 'Proj SIE Otp@70%';
COMPUTE Proj_SIE_Otp_70;
Proj_SIE_Otp_70 = NedbankVal.sum * 0.7;
ENDCOMP;
DEFINE Prox_Rates_Tax / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:no'] f=comma12.2 right 'Prox R&Ts @10%';
COMPUTE Prox_Rates_Tax;
Prox_Rates_Tax = Val.sum * 0.1;
ENDCOMP;
DEFINE Prox_Cost / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:no'] f=comma12.2 right 'Prox cost @9%';
COMPUTE Prox_Cost;
Prox_Cost = Val.sum * 0.09;
ENDCOMP;
DEFINE Proj_Loss_At_Nas90 / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:yes'] format=comma12.2 right 'Proj loss @NAS90%';
COMPUTE Proj_Loss_At_Nas90;
Proj_Loss_At_Nas90 = (Prox_Cost + Prox_Rates_Tax + Current_Bal.sum) - Proj_Nas_Otp_90;
if Proj_Loss_At_Nas90 < 0 then Proj_Loss_At_Nas90 = 0;
endcomp;
DEFINE Proj_Loss_At_Nas80 / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:yes'] format=comma12.2 right 'Proj loss @NAS80%';
COMPUTE Proj_Loss_At_Nas80;
Proj_Loss_At_Nas80 = (Prox_Cost + Prox_Rates_Tax + Current_Bal.sum) - Proj_Nas_Otp_80;
if Proj_Loss_At_Nas80 < 0 then Proj_Loss_At_Nas80 = 0;
endcomp;
DEFINE Proj_Loss_SIE / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:no'] format=comma12.2 right 'Proj loss @SIE';
COMPUTE Proj_Loss_SIE;
Proj_Loss_SIE = (Prox_Cost + Prox_Rates_Tax + Current_Bal.sum) - Proj_SIE_Otp_70;
if Proj_Loss_SIE < 0 then Proj_Loss_SIE = 0;
endcomp;
DEFINE Proj_Cr_Refund_NAS / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:yes'] format=comma12.2 right 'Proj Cr Refund @NAS';
COMPUTE Proj_Cr_Refund_NAS;
Proj_Cr_Refund_NAS = (Prox_Cost + Prox_Rates_Tax + Current_Bal.sum) - Proj_Nas_Otp_90;
if Proj_Cr_Refund_NAS > 0 then Proj_Cr_Refund_NAS = 0;
ENDCOMP;
DEFINE Proj_Cr_Refund_SIE / computed style(column)=[cellwidth=2.8cm cellheight=28 tagattr='wrap:yes'] format=comma12.2 right 'Proj Cr Refund @SIE';
COMPUTE Proj_Cr_Refund_SIE;
Proj_Cr_Refund_SIE = (Prox_Cost + Prox_Rates_Tax + Current_Bal.sum) - Proj_SIE_Otp_70;
if Proj_Cr_Refund_SIE > 0 then Proj_Cr_Refund_SIE = 0;
ENDCOMP;
run;
ods excel close;
goptions device=actximg;
options missing='';
ods listing close;
ods excel file="file1_&sysdate..xlsx"
options(embedded_titles='on' formulas='yes');
title color=black bold height=16pt wrap u=2 "Inflow";
ods excel options (sheet_name="Inflow" frozen_headers ='yes' );
PROC REPORT DATA=work.test missing headline headskip
style(header) = [fontweight=bold height=50 background=darkgreen foreground=white ];
column N1 Name Current_Bal Val Proj_Nas_Otp_90 Proj_Nas_Otp_80 Prox_Rates_Tax Prox_Cost Proj_Loss_At_Nas90 Proj_Loss_At_Nas80
Proj_SIE_Otp_70 Proj_Loss_SIE Proj_Cr_Refund_NAS Proj_Cr_Refund_SIE;
DEFINE N1 / display style(column)=[cellwidth=2.5cm cellheight=30];
DEFINE Name / display style(column)=[cellwidth=8cm cellheight=30 tagattr='wrap:no'] left;
DEFINE Current_Bal / sum style(column)=[cellwidth=2.5cm cellheight=30 tagattr="wrap:no"] f=comma12.2 right;
DEFINE Val / sum style(column)=[cellwidth=2.5cm cellheight=30 tagattr='wrap:no'] f=comma12.2 right;
DEFINE Proj_Nas_Otp_90 / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-1]*0.9'] f=comma12.2 right 'Proj NAS OTP@90%';
DEFINE Proj_Nas_Otp_80 / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-2]*0.8'] f=comma12.2 right 'Proj NAS OTP@80%';
DEFINE Proj_SIE_Otp_70 / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-7]*0.7'] f=comma12.2 right 'Proj SIE Otp@70%';
DEFINE Prox_Rates_Tax / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-3]*0.1'] f=comma12.2 right 'Prox R&Ts @10%';
DEFINE Prox_Cost / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:no' tagattr='formula:RC[-4]*0.09'] f=comma12.2 right 'Prox cost @9%';
DEFINE Proj_Loss_At_Nas90 / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Proj loss @NAS90%';
compute Proj_Loss_At_Nas90;
if Proj_Loss_At_Nas90.sum > 0 and upcase(_break_) NE '_RBREAK_' then do ;
call define('Proj_loss_At_Nas90.sum','style','style=[tagattr="formula:(RC[-1]+RC[-2]+RC[-6])-RC[-4]"]');
end;
endcomp;
DEFINE Proj_Loss_At_Nas80 / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Proj loss @NAS80%';
compute Proj_Loss_At_Nas80;
if Proj_Loss_At_Nas80.sum > 0 and upcase(_break_) NE '_RBREAK_' then do ;
call define('Proj_Loss_At_Nas80.sum','style','style=[tagattr="formula:(RC[-2]+RC[-3]+RC[-7])-RC[-4]"]');
end;
if Proj_Loss_At_Nas80.sum > 400000.00 and upcase(_break_) NE '_RBREAK_' then do ;
call define('Proj_Loss_At_Nas80.sum','style','style=[tagattr="formula:(RC[-2]+RC[-3]+RC[-7])-RC[-4]" background=light orange]');
end;
endcomp;
DEFINE Proj_Loss_SIE / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Proj loss @SIE';
compute Proj_Loss_SIE;
if Proj_Loss_SIE.sum > 0 and upcase(_break_) NE '_RBREAK_' then do ;
call define('Proj_Loss_SIE.sum','style','style=[tagattr="formula:(RC[-4]+RC[-5]+RC[-9])-RC[-1]"]');
end;
endcomp;
DEFINE Proj_Cr_Refund_NAS / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Proj Cr Refund @NAS';
compute Proj_Cr_Refund_NAS;
if Proj_Cr_Refund_NAS.sum < 0 and upcase(_break_) NE '_RBREAK_' then do ;
call define('Proj_Cr_Refund_NAS.sum','style','style=[tagattr="formula:(RC[-5]+RC[-6]+RC[-10])-RC[-8]"]');
end;
endcomp;
DEFINE Proj_Cr_Refund_SIE / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Proj Cr Refund @SIE';
compute Proj_Cr_Refund_SIE;
if Proj_Cr_Refund_SIE.sum < 0 and upcase(_break_) NE '_RBREAK_' then do ;
call define('Proj_Cr_Refund_SIE.sum','style','style=[tagattr="formula:(RC[-6]+RC[-7]+RC[-11])-RC[-3]"]');
end;
endcomp;
rbreak after / summarize skip style =[font_weight=bold fontsize=10.5pt bordertopstyle=solid borderbottomstyle=double
borderbottomwidth=2pt bordertopcolor=black borderbottomcolor=black] dol dul;
compute after;
Name= 'Total' ;
endcomp;
run;
ods excel close;
/**/
... View more