Excel Libname and specific range

[Doug____]

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

[PGStats]

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;

[Doug____]

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

[PGStats]

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.

[Ksharp · Accepted solution]

Yes. you can.


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

[Doug____]

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]

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

What is your code ?

[Doug____]

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