Using DDE to Read and Write to Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 176

The “Excel_starting_row” parameter is an integer that specifies the row number of the “Top Left Cell” of
the Excel range. Only the numeric portion of the R1C1 format of the cell formula is needed for the
parameter. These values are substituted into the R1C1 excel formula format. The value used to point to cell
“A1” as the upper left cell is “R1C1” and the lower right hand cell location is calculated from the PROC
CONTENTS listing of the input SAS file. This also implies that the macro will not work with SAS files
that are stored on sequential storage devices, like magnetic tape.
The “Excel_starting_column” parameter is an integer that specifies the Column number of the “Top Left
Cell” of the Excel range. Only the numeric portion of the R1C1 format of the cell formula is needed for the
parameter. These values are substituted into the R1C1 excel formula format. The value used to point to cell
“A1” as the upper left cell is “R1C1” and the lower right hand cell location is calculated from the PROC
CONTENTS listing of the input SAS file. This also implies that the macro will not work with SAS files
that are stored on sequential storage devices, like magnetic tape.
The NOTAB parameter for this macro provides a switch to turn on or off the placement of a tab character
between output data records when writing to the DDE destination.
FUNCTION:
This macro inspects the contents of the input SAS file and builds the DDE commands required to write the
selected variables from the input SAS dataset to the output Excel workbook and worksheet. All records are
written to the output Excel file but not all variables need to be selected from the input SAS file. The order
of the variables selected is the same as the order of the variables in the source SAS dataset. Therefore, if a
specific order is required for the output variables, it must be established within the source SAS file prior to
using this macro. A RETAIN statement can usually work to create the proper order if it needs to be
changed.
The NOTAB parameter for this macro provides a default value of “NOTAB=NOTAB” and can be changed
by providing the parameter in the macro call as “NOTAB=”. The FILENAME statement in the
SAS_2_Excel macro uses the NOTAB parameter macro value in the FILENAME statement proper.
Providing a null value for the NOTAB parameter just placed a space into the FILENAME statement. The
default is to place the characters “NOTAB” into the FILENAME statement. This applies the NOTAB
feature to the SAS FILENAME command. Any other value for the parameter will cause a syntax error in
the FILENAME statement. This parameter is optional.
Results:
Data from a SAS dataset will be written to an Excel workbook in the specified rows and columns of the
output Excel worksheet.
RESTRICTIONS:
This macro makes use of SAS macro variable names that begin or end with an underscore to avoid conflicts
with user-selected variable names. If a specific order is required for the output variables, it must be
established within the source SAS file prior to using this macro. All of the parameters except
“Excel_template_sheet” and “NOTAB” are checked to see if they are provided, but are not checked for
validity.