topic Re: Importing SAS column names from Excel row in SAS Procedures

Importing SAS column names from Excel row

cercig — Tue, 07 Jul 2015 09:13:34 GMT

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?

Re: Importing SAS column names from Excel row

RW9 — Tue, 07 Jul 2015 10:36:01 GMT

Well, firstly don't use Excel. It really isn't software designed for any purpose you can think of. Also, avoid using proc import/export - these are guessing procedures, they attempt to guess what your trying to do. If it is currently in Excel, then save as CSV. Then write a datastep with and infile to that CSV. You can then specify variables/lengths, how the data is read in, drop certain rows etc. Far more control. Here are examples of the import:

Re: Importing SAS column names from Excel row

cercig — Tue, 07 Jul 2015 11:02:16 GMT

Unfortunately the incoming data format is XLSX. Is there any way to convert the XLSX file to CSV file automatically in SAS-code? Manual processes like manually converting are the least desired techniques I guess

Re: Importing SAS column names from Excel row

Ksharp — Tue, 07 Jul 2015 12:12:51 GMT

Try option RANGE='Sheet1$A1:D100'

Re: Importing SAS column names from Excel row

RW9 — Tue, 07 Jul 2015 12:13:55 GMT

To be honest, the person supplying data in Excel (XLSX) form is the one who should accept responsibility for that any any possible errors which might arise from the conversion of an unstructured, non data structure format to a proper data transfer format. However, if you are willing to take that on, and I would not, then you would do a save as command from Excel and select CSV as the save file type. Note that this will create a text based, delimited file which can easily be read into SAS, however it will also flag certain data problems which you may not think about if you just use Excel, for instance each cell might have a different data type, could contain unusual characters etc. This come from the fact that Excel is unstructured, and shouldn't be used for the proper transfer of data.

Re: Importing SAS column names from Excel row

cercig — Tue, 07 Jul 2015 12:58:38 GMT

Thanks the option like RANGE='A3:D2000' worked. It was 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?

Anyway, it works now, and i can remove the other "." values with a simple if statement.

Thanks for clearing up the issues...

Re: Importing SAS column names from Excel row

Ksharp — Tue, 07 Jul 2015 13:15:46 GMT

Not sure. Maybe you should try another option MIXED=yes .

Re: Importing SAS column names from Excel row

cercig — Tue, 07 Jul 2015 13:26:42 GMT

I tried MIXED=YES and DATAROW=3 together before, but I got error for DATAROW. The error was about misusage of DATAROW option. I checked very much if I did a typo, but found nothing.

Now I am using MIXED and RANGE options together.

Re: Importing SAS column names from Excel row

Ksharp — Tue, 07 Jul 2015 13:33:09 GMT

option DATAROW is not for EXCEL engine, it is only for text file like CSV , TXT .

Re: Importing SAS column names from Excel row

cercig — Tue, 07 Jul 2015 13:43:13 GMT

Good to know! Thanks!