Can I export data to an Excel file with existing formulae while retaining formulae

[madara155]

I have an excel file with several columns: ID, NoOfItems,UnitPrice and Total (Values in column Total = values in column NoOfItems* values in column UnitPrice)

In SAS, monthly I get ID, NoOfItems and UnitPrice which I need to export to the above excel sheet (basically overwrite the file with new values). Can I do this operation, so that the Total value get calculated once the export is done? (i.e can I export data to Excel while retaining the operation of the existing formulae within the excel file)?

(I know I can find the TOTAL in SAS and export everything to Excel. The example I stated above is just a simplified version of my issue, where I actually need the existing formulae in the Excel sheet).

Thanks in advance for any support.

[AMSAS · Accepted solution]

“Excel”lent SAS® Formulas: The Creation and Export of Excel Formulas Using SAS Collin Elliot, Itron,... 
The above Global Forum paper has some good insights into how you can do this.

Here's a simple example I put together:

/* Create a simple dataset */
data export ;
	number1=123 ;
	number2=456 ;
	/* sum is the variable that will be a formula in Excel */
	sum=cats('=A2+B2') ;
run ;

/* Open Excel ODS */
ods excel file="C:\Users\snzroo\Documents\My SAS Files\9.4\export.xlsx"  ;

/* Send the output to Excel */
/* Note I drop the observation number (noobs) if you don't then the sum formula needs to be changed to '=B2+C2' */
proc print noobs ;
run ;

/* Close Excel ODS */
ods excel close;

[madara155]

Thank you very much for sharing that paper. It was very informative and I managed to get my work done 😀.

THANKS

[NithinRamu]

Hi,

You can write a dataset to Excel either using a library (with the excel or xlsx engine) or using ODS. The ODS has been shown in another reply. To do it with a library create a library using the assign project library task or using the code:

libname outxl excel "path-to-excel-workbook";

You might have to use xlsx instead of excel in the above statement. Then just save the table from SAS in this outxl library. Whatever table name you give will become the name of the worksheet in which the data is saved in the Excel workbook.

Here is the catch: when you write to Excel, any calculations or fields you calculated in Excel will be overwritten. However, this is not a problem: save the SAS table in a separate worksheet, which you will use as a bridging worksheet. In the worksheet you use to calculate the totals, reference the cells to the corresponding cells in the bridging worksheet (type in = in one of the cells, go to the other worksheet and select the cell to do this).

I hope this helps.

There are other ways using dynamic data exchange, not supported anymore so it is risky and coding in VBA. This is the simplest solution that came to my mind.