10-03-2017 03:22 PM
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;
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.
10-03-2017 03:54 PM
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.
10-03-2017 04:05 PM
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!
10-03-2017 04:03 PM
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.
10-03-2017 05:34 PM
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.
10-03-2017 04:08 PM
This might be worth looking at:
10-03-2017 05:44 PM
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.
10-03-2017 06:37 PM - edited 10-03-2017 07:50 PM
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;
10-04-2017 12:33 AM