DATA Step, Macro, Functions and more

Excel Libname and specific range

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Excel Libname and specific range

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.?


Accepted Solutions
Solution
‎08-05-2016 01:39 PM
Super User
Posts: 10,023

Re: Excel Libname and specific range

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


All Replies
Respected Advisor
Posts: 4,920

Re: Excel Libname and specific range

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
Contributor
Posts: 30

Re: Excel Libname and specific range

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

Respected Advisor
Posts: 4,920

Re: Excel Libname and specific range

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
Solution
‎08-05-2016 01:39 PM
Super User
Posts: 10,023

Re: Excel Libname and specific range

Yes. you can.


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

Contributor
Posts: 30

Re: Excel Libname and specific range

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?

 

Super User
Posts: 10,023

Re: Excel Libname and specific range

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

What is your code ?

Contributor
Posts: 30

Re: Excel Libname and specific range

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

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 754 views
  • 4 likes
  • 3 in conversation