BookmarkSubscribeRSS Feed
emcee825
Calcite | Level 5

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!

3 REPLIES 3
SASKiwi
PROC Star

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#n0f5mkuxiitygvn16n...

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;

emcee825
Calcite | Level 5

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.

JatinBansal
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3860 views
  • 0 likes
  • 3 in conversation