DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 6

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

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.

 

 

Super User
Posts: 3,771

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

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.

Occasional Contributor
Posts: 6

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

I get the same error.  Smiley Sad  

 

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!

Regular Contributor
Posts: 195

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

 

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. 

Occasional Contributor
Posts: 6

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

Posted in reply to error_prone

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!

Frequent Contributor
Posts: 102

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

This might be worth looking at:

 

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

Norman.
SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Occasional Contributor
Posts: 6

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

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.

 

Super User
Super User
Posts: 7,860

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

[ Edited ]

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;

 

 

Regular Contributor
Posts: 195

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

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.
Ask a Question
Discussion stats
  • 8 replies
  • 186 views
  • 0 likes
  • 5 in conversation