Help using Base SAS procedures

Importing SAS column names from Excel row

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

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?


Accepted Solutions
Solution
‎07-07-2015 08:12 AM
Super User
Posts: 9,682

Re: Importing SAS column names from Excel row

Try option  RANGE='Sheet1$A1Smiley Very Happy100'

View solution in original post


All Replies
Super User
Super User
Posts: 7,405

Re: Importing SAS column names from Excel row

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:

Contributor
Posts: 23

Re: Importing SAS column names from Excel row

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 Smiley Happy

Super User
Super User
Posts: 7,405

Re: Importing SAS column names from Excel row

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.

Solution
‎07-07-2015 08:12 AM
Super User
Posts: 9,682

Re: Importing SAS column names from Excel row

Try option  RANGE='Sheet1$A1Smiley Very Happy100'

Contributor
Posts: 23

Re: Importing SAS column names from Excel row

Thanks the option like RANGE='A3Smiley Very Happy2000' 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...

Super User
Posts: 9,682

Re: Importing SAS column names from Excel row

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

Contributor
Posts: 23

Re: Importing SAS column names from Excel row

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.

Super User
Posts: 9,682

Re: Importing SAS column names from Excel row

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

Contributor
Posts: 23

Re: Importing SAS column names from Excel row

Good to know! Thanks! Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 2309 views
  • 8 likes
  • 3 in conversation