My solution:
1. Import Excel worksheet with all columns as character by using the MIXED and GETNAMES options. For example:
PROC IMPORT DATAFILE='...'
OUT=rawdata_character
DBMS=EXCEL REPLACE;
SHEET="..."
MIXED=YES;
GETNAMES=NO;
RUN;
2. Assuming that the row of column headers is within the guessingrows range this technique will force all columns to be character. The SAS variable names will be F1, F2, F3, ...
Note that the formatted value in each cell will be imported. e.g., value=5.1324234, formatted cell=5.13, SAS value=5.13.
3. In data steps find the row containing the column headers (hard code it if it's always the same row), read the headers into SAS macro variables, and loop through the macro variables to create a SAS numeric variable for each column. When reading the row of headers remove special characters in order to produce a valid SAS name. When converting character to numeric (e.g., numeric_name = INPUT(character_name,BEST.) check if the conversion was successful (if character_name NE '' and numeric_name = . then conversion failed) and output unsuccessful conversions to a data file for further investigation.
You can enhance the data steps to handle issues such as inconsistent column header names by using a crosswalk mapping of each name to a standard name.
Bill.
