I am trying to create a SAS table from a XLSX Excel-file which looks like below. The SAS column names will be 3rd row in the Excel file and reading data from the 5th row.
A B C D F ...
1
2
3 Date Period Rate Rate down Rate up ...
4
5 2015-04-30 1 0.25 0.23 0.27 ...
6 2015-05-31 2 0.21 0.19 0.23 ...
. .........................................
. .........................................
I am using proc import
to gather the table as below:
proc import datafile = have out=want DBMS = excel;
GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; DATAROW=5;
run;
The problem is that Proc Import
takes the column names in the 3rd row in numeric format like the rest of the Excel file, so SAS puts "."
instead of column names like Date
or Rate
because SAS doesn't understand them as numeric values.
I found proc import
options like DATAROW=5
to read the data from the fifth row, and MIXED=YES
to indicate that the Excel-table include both numeric and character values. GETNAMES=YES
to get column names from the table, and SCANTEXT=YES
to scan text as you can understand. However, even with those options I got the same SAS table like below. The whole SAS-table is in numeric format, so it can't resolve names from Excel:
F1 F2 F3 F4 F5 ...
1 . . . . . ...
2 . . . . . ...
3 30APR2015 1 0.25 0.23 0.27 ...
4 31MAY2015 2 0.21 0.19 0.23 ...
. ...............................
. ...............................
Any idea about how to import the 3rd row of the XLSX file as my column name in the SAS table?