CODEWITHSUNDEEP
excelimportsasxlsx
user3714330
user3714330

Reputation: 689

Importing SAS column names from Excel row

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?

Upvotes: 2

Views: 5720

Answers (1)

user3714330
user3714330

Reputation: 689

OK. I found the solution. I should have just added a simple option like RANGE=A3:G2000. In a very strange matter, I got error with the option DATAROW=5, so I removed it. So the code becomes:

proc import datafile = have out=want DBMS = excel; 
GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; RANGE='A3:G2000'; 
run;

Now it works. But that RANGE option is not written on every webpage, it was difficult to find.

It was also very strange that SAS couldn't realize that character values like "Date" should be in character format. But it realizes it when you use a Range option?

Upvotes: 4

Related Questions