07-07-2015 05:13 AM
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 ...
3 Date Period Rate Rate down Rate up ...
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;
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
Rate because SAS doesn't understand them as numeric values.
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?
07-07-2015 06:36 AM
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:
07-07-2015 07:02 AM
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
07-07-2015 08:13 AM
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.
07-07-2015 08:58 AM
Thanks the option like RANGE='A32000' 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.
07-07-2015 09:26 AM
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.
Need further help from the community? Please ask a new question.