topic Re: How to get pcfiles libname engine to recognize long Excel sheet names in New SAS User

How to get pcfiles libname engine to recognize long Excel sheet names

cgmoore — Mon, 26 Nov 2018 21:32:05 GMT

Hello,

I'm trying to run a dynamic macro that appends all worksheets for each Excel workbook in a specified folder path.

I'm accomplishing this using a similar method to the one detailed here. I am using pcfiles engine for the libname as there is a mixture of .xlsx and .xls, and I'm running 64 bit SAS 9.4, so EXCEL libname engine doesn't work.

The problem I'm encountering is that some workbooks have sheet names of max length, which SAS does not find with a pcfiles libname statement. Is there a way to get a SAS libname statement to recognize max length sheet names so that I can utilize library memnames to dynamically append all sheets?

The attached Excel file has two sheets, one with a name too long to be caught by the below listed libname statement.

%let yourpath = c:\;
libname nametest pcfiles path = "&yourpath.example.xls";

Is there a way to get a pcfiles libname statement to recognize these longer names?

Is there an alternate method to accomplishing the same goal of dynamically appending all worksheets for all workbooks in a specified path? I'd prefer not to resort to VBA to alter the source files if at all possible.

Thanks,

Re: How to get pcfiles libname engine to recognize long Excel sheet names

Reeza — Mon, 26 Nov 2018 21:41:44 GMT

I think you'll need to use an external method to get the names. The funny thing is it does read them properly if you do know the names.

37   data t1;
38   set demo.'test test test test test test$'n;
39   run;

NOTE: There were 1 observations read from the data set DEMO.'test test test test test test$'n.
NOTE: The data set WORK.T1 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


40
41   data t2;
42   set demo.'test test test test test test t$'n;
43   run;

NOTE: There were 1 observations read from the data set DEMO.'test test test test test test t$'n.
NOTE: The data set WORK.T2 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Re: How to get pcfiles libname engine to recognize long Excel sheet names

SASKiwi — Mon, 26 Nov 2018 21:41:25 GMT

If you use the LIBNAME approach to access Excel then you are limited to a maximum of 32 characters for sheet names. There is no workaround. SAS is working on increasing this limit to 128 characters with SAS 9.5, hopefully due out in the next 12 months.