BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Doug____
Pyrite | Level 9

Can SAS import a specific range of cells from a workbook using the libname syntax? If so can it be done with non-standard tab names that contain punctuation, etc.?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
Yes. you can.


libname x excel 'c:\temp\temp.xls' ;
data want;
 set x.'Sheet1$'n(dbsastype=(g='char(10)' x='numeric'));
run;

View solution in original post

7 REPLIES 7
PGStats
Opal | Level 21

Yes, with the Excel engine, the syntax is:

 

libname xl Excel "c:\yourdir\yourFile.xlsx" access=readonly header=no;

 

data test;
set xl.'Sheet1$A1:Z50'n;
run;

PG
Doug____
Pyrite | Level 9

Thanks for the suggestion - within this - can I predefine columns as character and numeric? Can this be done if the columns are blank?

PGStats
Opal | Level 21

With the Excel engine, no (things might be different with the new xlsx engine, which I haven't tested.) The type of data columns is based on a scan to the first few lines of data. Assigning a format, even to empty cells, is often enough to get the correct column type.

PG
Ksharp
Super User
Yes. you can.


libname x excel 'c:\temp\temp.xls' ;
data want;
 set x.'Sheet1$'n(dbsastype=(g='char(10)' x='numeric'));
run;

Doug____
Pyrite | Level 9

Another related question,

 

I have imported xls workbooks (the same one) a number of times sucessfully and now I am getting the following message:

 

ERROR: File _IMEX_.'a$a27:r3000'n.DATA does not exist.

ERROR: Import unsuccessful. See SAS Log for details.

 

It does not happen all the time but sometimes. Any thoughts as to the cause? Network communication issues, perhaps?

 

Ksharp
Super User

How did you import Excel file ?  PROC IMPORT or LIBNAME ?

What is your code ?

Doug____
Pyrite | Level 9

I am currently using a PROC IMPORT step wtih SAS options to indicate which columns are numeric.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 4845 views
  • 4 likes
  • 3 in conversation