topic Re: Proc import for xls, variable names start from row 3, multiple sheets in SAS Procedures

Proc import for xls, variable names start from row 3, multiple sheets

emcee825 — Tue, 05 Nov 2013 22:46:58 GMT

I'm trying to import an xls file into SAS which has the variable names starting in row 3 (as opposed to row 2) and there are several sheets that contain the data that I need. However, there are a couple of sheets that I do NOT need to pull data from. When I specified a sheet name, I get an error in the log telling me, "that my sheet name is too long for a SAS name in this context." While I can probably very easily just change the sheet names, that wouldn't be a long term solution. So three questions:

1. How can I pull the variable names from row 3? I know I can use RANGE for that but if there are other options as well, I'm open to hearing them.

2. What can I do about my sheet name problem?

3. How can I import just the sheets that I need? My guess is to list each one individually but there has to be a more efficient way.

Thank you!

Re: Proc import for xls, variable names start from row 3, multiple sheets

SASKiwi — Tue, 05 Nov 2013 23:30:25 GMT

Try adding these statements to your PROC IMPORT: namerow=3; startrow = 3;

If your sheet names are greater than 32 characters then I don't think there is any workaround using PROC IMPORT, apart from renaming them to be no more than 32.

The 32 char limit applies to any tables referenced in the SAS environment, including external databases.

The only way I'm aware of to deal with external table names longer than 32 chars is to use SQL Passthru. Check out this link:

http://support.sas.com/documentation/cdl/en/acpcref/64792/HTML/default/viewer.htm#n0f5mkuxiitygvn16n6nwc3j2oqh.htm

A sample program could look like this:

PROC SQL;

CONNECT TO EXCEL (PATH='c:\sasdemo\sasdemo.xlsx');

SELECT * FROM CONNECTION TO EXCEL

(SELECT * FROM A_Sheet_Name_Longer_Than_Thirty_Two_Chars);

DISCONNECT FROM EXCEL;

QUIT;

Re: Proc import for xls, variable names start from row 3, multiple sheets

emcee825 — Tue, 12 Nov 2013 19:34:15 GMT

Hi SASKiwi,

Thanks so much for your response. I tried using the sample code you provided for PROC SQL but am given the following error in the log:

ERROR: Describe: The Microsoft Office Access database engine could not find the object

'QUANT_Screening_Roster_081713'. Make sure the object exists and that you spell its

name and the path name correctly.

I'm confused as to why SAS thinks I'm looking for an Access db (I used your code, exactly, and just changed the file path). I did double check to make sure that I spelled everything correctly by copying and pasting the file path and the sheet name but no luck. As far as I can tell from the SAS help & documentations, everything looks correct...

Also, while we're on this topic, I would like to import multiple sheets. I'm not sure what the syntax would look for that-- would I do multiple (SELECT * FROM Sheet_Name);? Would you happen to know?

Thanks again, it's much appreciated.

Re: Proc import for xls, variable names start from row 3, multiple sheets

JatinBansal — Thu, 19 Dec 2013 11:38:29 GMT

The following will work with the given changes:

PROC SQL;

CONNECT TO EXCEL (PATH='c:\sasdemo\sasdemo.xlsx');

SELECT * FROM CONNECTION TO EXCEL

(SELECT * FROM [A_Sheet_Name_Longer_Than_Thirty_Two_Chars$]);

DISCONNECT FROM EXCEL;

QUIT;

Also, for Names on third row, namerow and datarow options can be used in the same sql procedure in connect statement. For multiple sheets, macro can help.