2 users online | 2 Guests and 0 Registered

Is it possible to create formulae in Excel output and apply formatting from SAS?


The use of the ODS destinations of ODS EXCEL and ODS TAGSETS.EXCELXP have allowed for greater scope to share output, but data is typically static.  It is however possible to create data within Excel cells which contain formulae, as well as apply formatting, which reduces the need to perform post-processing on the output file.

The following code allows for the creation of formulae, with relative cell references (although absolute references are also possible).

ods excel file = 'c:\cars.xls' ;
proc report data = sashelp.cars nowd ;
column make
       model
       invoice
       mpg_city
       mpg_highway
       mpg_mean    /*** This will be a COMPUTED column containing the formula ***/
       ;
define make        /display ;
define model       / display ;
define invoice     / display  style={tagattr='format:###,##0'                      }             ;
define mpg_city    / display  style={tagattr='format:0.00'                         }             ;
define mpg_highway / display  style={tagattr='format:0.00'                         }             ;
define mpg_mean    / computed style={tagattr='format:0.00 formula:rc[-2]+rc[-1]/2' } 'MPG(Mean)' ;
run ;
ods excel close ;

The computed column uses two relative cell references using the Excel Row / Column (RC) syntax.  RC[-2] is the column two previous in the same row.  To use absolute references the syntax  would be R##C## replacing the hashes with the corresponding absolute row and column number.

The formatting has been applied to the INVOICE column with placeholders to display comma separators only where required.  The formatting on the three MPG columns will always show two decimal places.

Author:
Alan D Rudland
Revision:
1.5
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.