Outputting Formulae to Microsoft Excel
The ExcelXP tagset is a popular method of outputting data to Microsoft® Excel from SAS. Writing data values is straightforward, but can we output an Excel formula? The answer is yes!
The TAGATTR= style attribute of the ExcelXP tagset can be used to specify a format and/or formula for a specific cell. In the example below we are going to output the SASHELP.DEMOGRAPHICS dataset to Excel and automatically write, for each row of data, Excel formulae that will calculate the values for our two new fields, rough_estimate_pop_2015 (current population * 1.2) and the more complex calculated_pop_2015 (current population * growth rate ^ 10 years.)
ods tagsets.excelxp
options(frozen_headers='Yes' autofilter='All'
embedded_titles='No')
file="C: (File Path) .xls" style=journal;
ods noproctitle;
data new_dataset;
set sashelp.demographics;
/* Introducing our new variables,
which must be given non-blank default values*/
rough_estimate_pop_2015=0;
calculated_pop_2015=0;
run;
proc print data=new_dataset noobs;
var pop / style={tagattr='format:#,##0'};
var popAGR;
var rough_estimate_pop_2015 / style=
{tagattr='formula:RC[-2]*1.2 format:#,##0'};
var calculated_pop_2015 / style=
{tagattr='formula:((RC[-3])*(RC[-2] +1)^10) format:#,##0'};
run;
ods tagsets.excelxp close;
Note that the TAGATTR style element is specified on the style option of the VAR statement which contains the column which we want to contain the formula. We must employ the Excel RC style of cell referencing. This can use absolute references, eg =R20C1 + R4C2, or cell references that are relative to the cell that the formula is entered into. In the example above, RC[-2] species the cell 2 columns to the left of and in the same row as the formula-containing cell.
Note also the application of an Excel numeric comma format in the TAGATTR statement.
This will write formulae in the rough_estimate_pop and calculated_pop columns as shown below:
