topic Re: Excel Libname and specific range in SAS Programming

Excel Libname and specific range

Doug____ — Fri, 13 May 2016 14:11:08 GMT

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

Re: Excel Libname and specific range

PGStats — Fri, 13 May 2016 14:17:08 GMT

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;

Re: Excel Libname and specific range

Doug____ — Fri, 13 May 2016 14:39:05 GMT

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

Re: Excel Libname and specific range

PGStats — Fri, 13 May 2016 15:50:55 GMT

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.

Re: Excel Libname and specific range

Ksharp — Sat, 14 May 2016 08:16:02 GMT

Yes. you can.


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

Re: Excel Libname and specific range

Doug____ — Mon, 23 May 2016 22:28:50 GMT

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?

Re: Excel Libname and specific range

Ksharp — Tue, 24 May 2016 00:50:45 GMT

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

What is your code ?

Re: Excel Libname and specific range

Doug____ — Thu, 02 Jun 2016 18:39:51 GMT

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