Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

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:

Outputting Formulae to Microsoft Excelv1.0