Database Reference
In-Depth Information
PARAMETERS:
This macro accepts the following parameters:
file_ref
- FILEREF name up to 8-character SAS reference name
●
path_name
- full path of Excel file
●
file_name
- full name of Excel file
●
Sheet_name
- Workbook sheet name (default name = Sheet1)
●
Start_row_Col
- Starting input/output data row and column (top-left of Excel range)
●
End_row_col
- Ending input/output data row and column (bottom-right of Excel range)
●
no_tab
- YES/NO answer, where YES = use notab option and NO = do not use
notab
●
•
L_REC_L
- Logical Record Length value in the range of 1 byte to 1 gigabyte-1
This macro accepts a fileref name used to build an Excel DDE-triplet to point to a data range of Excel
worksheet cells from SAS. The macro verifies that a FILEREF name was provided, but does not verify that
the FILEREF is valid. Any valid SAS fileref can be provided. If the FILEREF is the same as one already
used, it will override the previous FILEREF and DDE triplet if the FILEREF pointed to a DDE triplet.
The second parameter is the full path and file name of an existing Excel file to open for use by SAS to
interface with Excel. The macro verifies that a path and file name are provided, but does not verify that the
path or file exists.
The third parameter is the full file name of an existing Excel file to open for use by SAS to interface with
Excel. The macro verifies that a path and file name are provided, but does not verify that the path or file
exists.
The Sheet_name parameter is optional and points to the sheet name “Sheet1” as the default sheet name in
the Excel file. The macros were not tested with file or sheet names that had embedded blanks; this was left
as an enhancement that you can work on to make the macros your own.
The “Start_row_col” parameter points to the upper left Excel cell in the DDE range to be defined. The
format of these values must be the R1C1 Excel formula format. The value used to point to cell “A1” as the
upper left cell is “R1C1” and the cell “D10” is referenced as “R10C4” for the lower right cell of the range.
(The notation stands for “Row” number “Column” number.)
The “End_row_col” parameter points to the lower-right Excel cell in the DDE range to be defined. The
format of these values must be the R1C1 Excel formula format. The value used to point to cell “A1” as the
upper left cell is “R1C1” and the cell “D10” is referenced as “R10C4” for the lower right cell of the range.
(The notation stands for “Row” number “Column” number.)
The NOTAB parameter accepts a value of “NO” to apply the NOTAB feature to the SAS FILENAME
command. Any other value for the parameter will suppress the “NOTAB” option on the FILENAME
statement. This parameter is optional.
The L_REC_L parameter allows you to change the size of the output character fields from 256 bytes to the
amount you specify in this parameter. (L_REC_L stands for Logical Record Length). The Excel default
length is 256 bytes.
Result:
This macro resets the FILENAME to point to a specific set of cells in the Excel workbook. SAS INPUT
and PUTcommands control whether data is read from Excel or written to Excel.
RESTRICTIONS:
The format of the row and column values must be the R1C1 excel formula format.
Search WWH ::
Custom Search