Proc report Formulae

[DeepaG]

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;

/**/

[JosvanderVelden]

Is it not possible to simplify your question and create a sample spreadsheet (ex. 3 observations, 3 columns) with ODS that details your issue?

[DeepaG]

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 account Name Balance Val Otp_90 Otp_80 Otp_70 Rates Prox_Cost Loss_At_90 Loss_At_80
       Proj_Loss_SIE Proj_Cr_Refund_NAS Proj_Cr_Refund_SIE;

DEFINE account / display style(column)=[cellwidth=2.5cm cellheight=30];
DEFINE Name / display style(column)=[cellwidth=8cm cellheight=30 tagattr='wrap:no'] left;
DEFINE Balance / 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 Otp_90 / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-1]*0.9'] f=comma12.2 right 'OTP@90%';
DEFINE Otp_80 / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-2]*0.8'] f=comma12.2 right 'OTP@80%';
DEFINE Otp_70 /  sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-7]*0.7'] f=comma12.2 right 'Otp@70%';
DEFINE Rates / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes' tagattr='formula:RC[-3]*0.1'] f=comma12.2 right 'Rates';
DEFINE Prox_Cost / sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:no' tagattr='formula:RC[-4]*0.09'] f=comma12.2 right 'cost';
DEFINE Loss_At_90 / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Loss @90%'; 
   compute Loss_At_90;
       if Loss_At_90.sum > 0 and upcase(_break_) NE '_RBREAK_' then do ;
	   call define('Loss_At_90.sum','style','style=[tagattr="formula:(RC[-1]+RC[-2]+RC[-6])-RC[-4]"]');
	   end;
  endcomp;
DEFINE Loss_At_80 / analysis sum style(column)=[cellwidth=2.8cm cellheight=30 tagattr='wrap:yes'] format=comma12.2 right 'Loss @80%'; 
   compute Loss_At_80;
   	  if Loss_At_80.sum > 0 and upcase(_break_) NE '_RBREAK_' then do ;
      call define('Loss_At_80.sum','style','style=[tagattr="formula:(RC[-2]+RC[-3]+RC[-7])-RC[-4]"]');
	  end;
	  if Loss_At_80.sum > 400000.00 and upcase(_break_) NE '_RBREAK_' then do ;
	  call define('Loss_At_80.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 '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;

run;
ods excel close;

/**/

The attached file is a sample of the data that I want to create...

If I change the value under the rates column as an example, is it possible to have all the other computed columns updated automatically as well?

My current code does add in the formulae & calculate the values correctly as I want, however my client wants to be able to edit values on the output spreadsheet such as the rates and cost columns.

[Cynthia_sas]

Hi:

  I guess I am still not understanding your question. Here's what I think you are asking for:

1) You create an Excel workbook using PROC REPORT and SAS. You calculate values for some of the columns in DEFINE statements and for other columns using COMPUTE blocks, CALL DEFINE and TAGATTR with Excel formulas.

2) You provide the workbook created in #1 to your client

3) Your client wants to CHANGE the formulas and values after they open #1.

4) and then what???????

Regarding #4 -- What do you want to happen?? Can your client edit the Workbook/worksheet? If so, I don't understand the question. If they want to edit the fields and values, they should be able to do so.

  However, once SAS and PROC REPORT are done creating the Excel file in #1, then SAS and PROC REPORT are out of the process. After #3, the data and formulas have been changed in Excel. Now you have lost all your data integrity. There is no record of the changes. The data that you originally used is still on the server or on the system. If you run the PROC REPORT code again, then the formulas in the original code from #1 is the code that will be used. It sounds to me almost as if you want the client's changes to be written back to SAS or the client's changed formulas to be used automatically. SAS and PROC REPORT will not do that.

Sorry I don't really get what your question is.

Cynthia

[DeepaG]

Hi Cynthia

Thanks for your reply.

Yes, your interpretation of what needs to happen is correct.

The client is able to edit the worksheet that SAS creates, and change values accordingly to actual values.

However, the formulae do not then update as SAS has already created them.

From your reply, I take it that this is not something that can be automated.

Thank you.

[Cynthia_sas · Accepted solution]

Hi:
Sorry, it's still not clear what you mean. When I edit an Excel file, that has formulas, whether created with SAS or not, if I change the formula, I have to SAVE the changed Excel file and my changed formulas are saved -- in Excel.

I don't understand what you mean when you say "the formulae do not then update" do you mean that they are saving the Excel file after making their changes and the file reverts to the original formulas? That sounds like they might not have write access to the file. Or, it sounds possible that the Excel file is being created by a stored process and the stored process runs and re-creates the file with the original formulas the next time they open the file?

I can't say whether "this is something that can be automated" because it's still not clear to me where you want the update to be reflected. If you want the changed formulas to be saved in the Excel file, the users need to make sure they have write access to the Excel file and that the file is not being refreshed behind the scenes by the original PROC REPORT program. If they want to change the formulas used by SAS in the PROC REPORT program, then the users need to inform the developer of the program that the formulas need to change. As far as I know, there is no way to link the formulas in the Excel sheet back to SAS code to change the code.

Cynthia

[DeepaG]

Hi Cynthia

Yes, I think you are correct. It is possible that the user of the file does not have write access to change formulas & update the file.

I will check on that.

Thanks so much!

DeepaG