BookmarkSubscribeRSS Feed
mendezla
Fluorite | Level 6

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.

 

 

8 REPLIES 8
SASKiwi
PROC Star

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.

mendezla
Fluorite | Level 6

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!

error_prone
Barite | Level 11

 

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. 

mendezla
Fluorite | Level 6

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!

Norman21
Lapis Lazuli | Level 10

This might be worth looking at:

 

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

Norman.
SAS 9.4 (TS1M6) X64_10PRO WIN 10.0.17763 Workstation

mendezla
Fluorite | Level 6

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.

 

Tom
Super User Tom
Super User

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;

 

 

error_prone
Barite | Level 11
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3313 views
  • 1 like
  • 5 in conversation