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

RESTRICTIONS:
The FILEREF provided to this macro must be open, or unpredictable results may occur.

11.4.6 SAS Macro to Write All or Selected Variables to an Excel Output
Workbook

Macro Name:
SAS_2_Excel

FUNCTION:
This macro is designed only to transfer data from SAS to Excel. The 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.

PARAMETERS:
● SAS_input_file - SAS input file

Excel_template_path - Excel format file path

●

Excel_template_name - Excel format file name

●

Excel_template_sheet - Excel format sheet name

●

Excel_output_path - Excel output file path

●

Excel_output_name - Excel output file name

●

Excel_starting_row - First Excel output row ●

Excel_starting_column - First Excel output col

●

• notab=notab

- switch tab/notab output

The first parameter, “SAS_input_file”, is the name of a SAS file. This is a fully qualified SAS name. It can
be a file from the Work library, or another predefined libref, or from the USER libref or from a user-
defined libref. This macro allows the inclusion of SAS dataset options that deal with dataset variable names
like DROP, KEEP, or RENAME. But, data set options that deal with the data values are not acceptable to
this macro, like the WHERE= clauses. You could add a feature to this macro that would make those
acceptable.

The “Excel_template_path” parameter refers to a directory path where an input Excel file exists that can
have preexisting formatting to be used as a template for the output Excel file layout of the output Excel
dataset.

The “Excel_template_name” parameter refers to an input Excel file that can have preexisting formatting to
be used as a template for the output Excel file layout of the output Excel dataset.

The “Excel_template_sheet” parameter is optional and points to the sheet name. The name “Sheet1” is used
as the default sheet name in the Excel file.

The “Excel_output_path” parameter is the directory path of the output file. This can be the same as the
input parameter “Excel_template_path” because there is no restriction on the usage. This macro was not
tested with file or sheet names that had embedded blanks.

The “Excel_output_name” parameter is the file name of the output file. This can be the same as the input
parameter “Excel_template_name” because there is no restriction on the usage. This macro was not tested
with file or sheet names that had embedded blanks.