topic Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh in SAS Programming

How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksheets

mendezla — Tue, 03 Oct 2017 19:22:47 GMT

Hello Everyone,

I'm trying to utilize the Libname statement to read in worksheet names from an Excel workbook. I am successful reading in the worksheet names; however, one worksheet name has a name that is does not comply with SAS data set naming conventions. When the library is created, I can see the worksheet name (now a dataset name) but I cannot change it or delete it. So, when I need to import the data using Proc Import, it will not work because of the data set name. (I am create a macro variable that reads in all of the datasets within the library, and I use that macro variable within a macro that uses Proc Import).

Here is libname statement (it's a fake one since I cannot divulge the actual Excel worksheet name):

libname PAIN XLSX "C:\Users\lmendez\Documents\XXXX\2017\Q2_PAIN\XXXX_Tracking_PAIN_2017Q2.xlsx";

The worksheet name is something like PAIN_1(varname). The parenthesis is what is causing the issue. When the library is created the worksheet name appears just fine, but when I try to run proc import, it gives me an error.

Here is the proc import:

PROC IMPORT OUT= PAIN.PAIN_1(VAR NAME)
   DATAFILE = "C:\Users\lmendez\Documents\XXXX\2017\Q2_PAIN\XXXX_Tracking_PAIN_2017Q2.xlsx" DBMS = xlsx REPLACE; 
   SHEET = "PAIN_1(VAR NAME)"; 
   GETNAMES = YES; 
RUN;

And here is the error msg:

71       PROC IMPORT OUT= PROC IMPORT OUT= cPAIN.PAIN_1(VAR NAME)
                                                       -----
                                                       22
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
ERROR 22-7: Invalid option name VAR.

Some notes: I'm using PC SAS 9.4 in Windows. I HAVE to use Excel Workbooks as I am QCing them. I don't have the luxury to change the worksheet name as there are about 324 workbooks that I have to read in, plus the client wants the name in Excel with the parenthesis. I have tried to use Proc Data sets to change the name, delete the data set (so I could try to recreate it), etc. but I get an error stating:

NOTE: Deleting CPAIN1.'ZIP_SUBGRP_OXYCO(CRUSH RESIS)'n (memtype=DATA).
ERROR: PAIN.'PAIN_1(VAR NAME)'n.DATA cannot be deleted because files cannot be deleted from the PAIN library.

Any thoughts on how to workaround this issue are appreciated.

Thank you.

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

SASKiwi — Tue, 03 Oct 2017 19:54:35 GMT

There are a couple of ways to deal with this:

1. Try this way of creating a non-standard dataset name

PROC IMPORT OUT= 'PAIN.PAIN_1(VAR NAME)'n

2. Provide a SAS-standard dataset name instead.

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

error_prone — Tue, 03 Oct 2017 20:03:03 GMT

You don't need proc import after using libname. Copy the worksheets to a sas-base-library, then renaming should be possible. Depending on the number of files/sheets using proc import (as @SASKiwi suggested) can be the easier way.

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

mendezla — Tue, 03 Oct 2017 20:05:42 GMT

I get the same error. 😞

I am going to have them change all of the worksheet names; however, I'd love to find a way to do this so we don't have to always change the worksheet name in the future.

Thank you for your help!

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

Norman21 — Tue, 03 Oct 2017 20:08:12 GMT

This might be worth looking at:

http://www2.sas.com/proceedings/sugi31/115-31.pdf

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

mendezla — Tue, 03 Oct 2017 21:34:17 GMT

I'm not sure I understand how I don't need a proc import after the libname statement. I need to load the data, and the libname statement only loads the structure of the data sets. I 'm going to try to copy the data sets, although I'm still having a hard time conceptualizing that.

I'll let you know if I can get that to work.

Thank you!

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

mendezla — Tue, 03 Oct 2017 21:44:34 GMT

Thank you for the reference. I actually have Vince's paper, but he uses ODS and I had already coded all of my programs with macros using the libname xlsx engine. Although, since I haven't been able to solve my problem, I may want to code it the way Vince illustrates it in his paper. This will provide me the workaround I need for the non-standard SAS naming convention worksheet name.

Thank you for providing the reference! I appreciate it.

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

Tom — Tue, 03 Oct 2017 23:50:19 GMT

Your current code is trying to read from and write to the same worksheet.

What are you actually trying to do?

If you are trying to copy from one workbook to another workbook then why not just use PROC COPY? You can tell SAS it is ok to use some nonstandard strings for member names by setting the VALIDMEMNAME option to EXTEND. You can tell SAS it is ok to use non-standard variable names by setting VALIDVARNAME option to ANY. You can refer to non-standard names (variable names or member names) using name literals.

So perhaps you just want something like this?

%let dir=C:\Users\lmendez\Documents\XXXX\2017\Q2_PAIN;
options validmemname=extend ;
libname IN XLSX "&dir\workbook1.xlsx";
libname OUT XLSX "&dir\workbook2.xlsx";
proc copy inlib=in outlib=out ;
  select "PAIN_1(VAR NAME)"n ;
run;

Here is an example of creating a sheet with () in the name using XLSX libname engine.

%let dir=%sysfunc(pathname(work));
libname out xlsx "&dir/test1.xlsx";
options validmemname=extend ;
data out.'class(test1)'n ;
 set sashelp.class;
run;

Re: How do I deal with nonstandard worksheet names using the Libname XLXS engine to read mult worksh

error_prone — Wed, 04 Oct 2017 04:33:29 GMT

A libref, assigned by libname statement, can be seen as a shortcut to a location in which datasets are stored. The engine used in a libname statement empowers SAS to see an Excel-file as storage location, so that the worksheets can be used like normal datasets, but with some restrictions.