LIBNAME XLSX Engine with Tabs Containing Spaces

[djbateman]

My company used to always use PROC IMPORT to read in an Excel file, but sometimes we have run into the issue where SAS will not allow a file path + file name to be more than 201 characters long.  We thought it would be wise to use the LIBNAME statement with the XLSX engine.  I have attached a screenshot of my code and the DTS library that is created.  My problem is that files are not always created with standard SAS naming conventions.  In other words, a tab is named "DATASET VARIABLE ATTRIBUTES", but SAS cannot recognize this as a dataset due to the spaces, so it is inaccessible.  I know that when SAS reads in an Excel file and the column names are not SAS standard, it will make them standard by replacing spaces, commas, hyphens, etc. with underscore "_".  Is there a way for SAS to do this with the tab names in an Excel file?  I'm just wondering if this method of importing an Excel file will truly work for us.

Excel File Tab Names

SAS Code and DTS Library Dataset Names

[ballardw]

Your screenshot didn't make it.

I'm not sure if the system option VALIDMEMNAME=COMPATIBLE will do what you need but that would be a place to start.

Note that this option is one that your SAS admin may restrict.

I've never relied on Proc Import for dealing with Excel because of too many inconsistencies in results though.

[djbateman]

Thanks. I attempted to upload my screenshot again. I think it's there now.

I tried your VALIDMEMNAME=COMPATIBLE option, and nothing happened.

[Tom · Accepted solution]

There is no picture attached. Your attached document is not viewable.

To post a picture click on the camera icon.

You will probably need to use the VALIDMEMNAME=EXTEND option.

You could try automating the renaming of the dataset to valid SAS names.

For example by replacing any non letter/digit with underscore.  Here is code that will copy the sheets to WORK library.

%let optsave=%sysfunc(getoption(validmemname,keyword));
options validmemname=extend;
libname x xlsx 'myfile.xlsx';
proc contents data=x._all_ noprint out=contents ; run;

filename code temp;
data _null_;
  set contents(keep=libname memname) ;
  by memname ;
  if first.memname;
  length src target $80 ;
  src=catx('.',libname,nliteral(memname));
  target=catx('.','work',translate(trim(memname),repeat('_',31),compress(memname,,'da')));
  file code;
  put 'data ' target '(label=' memname :$quote. '); set ' src '; run;' ;
run;

%include code / source2;

options &optsave;

[djbateman]

I hope I was finally able to get my screen shots uploaded on my original message.

I tried your code, and nothing changed.  MEMNAME still has spaces and commas even though NAME is doing what I would expect to happen with MEMNAME.

[Tom]

Post the log from the job that tried to copy the data from the XLSX file to datasets that have member names without spaces.

Copy the text from the log and post it using the Insert Code button (looks like < / > ).  

[djbateman]

838   %let optsave=%sysfunc(getoption(validmemname,keyword));
839   options validmemname=extend;
840   libname x xlsx 'S:\cdm\Programming\445\109\DTS\ECG\VX18-445-109_Standard Digital ECG Data
840 ! Transfer Specification Final v1.0.xlsx';
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: S:\cdm\Programming\445\109\DTS\ECG\VX18-445-109_Standard Digital ECG Data
      Transfer Specification Final v1.0.xlsx
841   proc contents data=x._all_ noprint out=contents ; run;

NOTE:    Variable Name Change.  CODELIST NAME -> CODELIST_NAME
NOTE:    Variable Name Change.  VENDOR CONTACTS -> VENDOR_CONTACTS
NOTE:    Variable Name Change.  DTS Sign Off Required -> DTS_Sign_Off_Required
NOTE:    Variable Name Change.  Req, Exp, Perm? -> VAR8
NOTE:    Variable Name Change.  GENERAL REQUIREMENTS -> GENERAL_REQUIREMENTS
NOTE:    Variable Name Change.  KEY VARIABLE ORDER -> KEY_VARIABLE_ORDER
NOTE:    Variable Name Change.  EGORRESU/ EGSTRESU -> EGORRESU__EGSTRESU
NOTE:    Variable Name Change.  # -> _
NOTE:    Variable Name Change.  FILE TRANSFER SPECIFICATIONS: -> FILE_TRANSFER_SPECIFICATIONS_

NOTE:    Variable Name Change.  DESCRIPTION: -> DESCRIPTION_
NOTE:    Variable Name Change.  # -> _
NOTE:    Variable Name Change.  GENERAL OR STUDY SPECIFIC -> GENERAL_OR_STUDY_SPECIFIC
NOTE:    Variable Name Change.  VERSION # -> VERSION__
NOTE:    Variable Name Change.  VERSION DATE -> VERSION_DATE
NOTE:    Variable Name Change.  COMMENTS (for visits) -> COMMENTS__for_visits_
NOTE: The data set WORK.CONTENTS has 56 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.23 seconds
      cpu time            0.21 seconds


842
843   filename code temp;
844   data _null_;
845     set contents(keep=libname memname) ;
846     by memname ;
847     if first.memname;
848     length src target $80 ;
849     src=catx('.',libname,nliteral(memname));
850     target=catx('.','work',translate(trim(memname),repeat('_',31),compress(memname,,'da')));
851     file code;
852     put 'data ' target '(label=' memname :$quote. '); set ' src '; run;' ;
853   run;

NOTE: The file CODE is:
      Filename=F:\SASTemp\SAS Temporary Files\_TD22200_AWS1PSASCOMPW01_\#LN00178,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=15Apr2021:13:32:57,
      Create Time=15Apr2021:13:32:57

NOTE: 10 records were written to the file CODE.
      The minimum record length was 52.
      The maximum record length was 121.
NOTE: There were 56 observations read from the data set WORK.CONTENTS.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


854
855   %include code / source2;
NOTE: %INCLUDE (level 1) file CODE is file F:\SASTemp\SAS Temporary
      Files\_TD22200_AWS1PSASCOMPW01_\#LN00178.
856  +data work.CODELISTS (label="CODELISTS" ); set X.CODELISTS ; run;
NOTE:    Variable Name Change.  CODELIST NAME -> CODELIST_NAME

NOTE: The import data set has 8 observations and 5 variables.
NOTE: There were 8 observations read from the data set X.CODELISTS.
NOTE: The data set WORK.CODELISTS has 8 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


857  +data work.CONTACTS (label="CONTACTS" ); set X.CONTACTS ; run;
NOTE:    Variable Name Change.  VENDOR CONTACTS -> VENDOR_CONTACTS
NOTE:    Variable Name Change.  DTS Sign Off Required -> DTS_Sign_Off_Required

NOTE: The import data set has 8 observations and 8 variables.
NOTE: There were 8 observations read from the data set X.CONTACTS.
NOTE: The data set WORK.CONTACTS has 8 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


858  +data work.DATASET_VARIABLE_ATTRIBUTES (label="DATASET VARIABLE ATTRIBUTES" ); set
858 !+X."DATASET VARIABLE ATTRIBUTES"N ; run;
NOTE:    Variable Name Change.  Req, Exp, Perm? -> VAR8
NOTE:    Variable Name Change.  GENERAL REQUIREMENTS -> GENERAL_REQUIREMENTS
NOTE:    Variable Name Change.  KEY VARIABLE ORDER -> KEY_VARIABLE_ORDER

NOTE: The import data set has 24 observations and 11 variables.
NOTE: There were 24 observations read from the data set X.'DATASET VARIABLE ATTRIBUTES'n.
NOTE: The data set WORK.DATASET_VARIABLE_ATTRIBUTES has 24 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


859  +data work.EGTEST_CODELIST (label="EGTEST CODELIST" ); set X."EGTEST CODELIST"N ; run;
NOTE:    Variable Name Change.  EGORRESU/ EGSTRESU -> EGORRESU__EGSTRESU

NOTE: The import data set has 21 observations and 7 variables.
NOTE: There were 21 observations read from the data set X.'EGTEST CODELIST'n.
NOTE: The data set WORK.EGTEST_CODELIST has 21 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


860  +data work.FILE_TRANSFER_SPECS (label="FILE TRANSFER SPECS" ); set X."FILE TRANSFER SPECS"N
860 !+ ; run;
NOTE:    Variable Name Change.  # -> _
NOTE:    Variable Name Change.  FILE TRANSFER SPECIFICATIONS: -> FILE_TRANSFER_SPECIFICATIONS_

NOTE:    Variable Name Change.  DESCRIPTION: -> DESCRIPTION_

NOTE: The import data set has 14 observations and 4 variables.
NOTE: There were 14 observations read from the data set X.'FILE TRANSFER SPECS'n.
NOTE: The data set WORK.FILE_TRANSFER_SPECS has 14 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.03 seconds


861  +data work.GENERAL_SPECS (label="GENERAL SPECS" ); set X."GENERAL SPECS"N ; run;
NOTE:    Variable Name Change.  # -> _
NOTE:    Variable Name Change.  GENERAL OR STUDY SPECIFIC -> GENERAL_OR_STUDY_SPECIFIC

NOTE: The import data set has 7 observations and 5 variables.
NOTE: There were 7 observations read from the data set X.'GENERAL SPECS'n.
NOTE: The data set WORK.GENERAL_SPECS has 7 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


862  +data work.INDEX (label="INDEX" ); set X.INDEX ; run;

NOTE: The import data set has 9 observations and 3 variables.
NOTE: There were 9 observations read from the data set X.INDEX.
NOTE: The data set WORK.INDEX has 9 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds


863  +data work.RESULTS_CODELIST (label="RESULTS CODELIST" ); set X."RESULTS CODELIST"N ; run;

NOTE: The import data set has 79 observations and 4 variables.
NOTE: There were 79 observations read from the data set X.'RESULTS CODELIST'n.
NOTE: The data set WORK.RESULTS_CODELIST has 79 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.21 seconds
      cpu time            0.18 seconds


864  +data work.VERSION_HISTORY (label="VERSION HISTORY" ); set X."VERSION HISTORY"N ; run;
NOTE:    Variable Name Change.  VERSION # -> VERSION__
NOTE:    Variable Name Change.  VERSION DATE -> VERSION_DATE

NOTE: The import data set has 3 observations and 5 variables.
NOTE: There were 3 observations read from the data set X.'VERSION HISTORY'n.
NOTE: The data set WORK.VERSION_HISTORY has 3 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


865  +data work.VISIT__TPT_CODELISTS (label="VISIT, TPT CODELISTS" ); set X."VISIT, TPT
865 !+CODELISTS"N ; run;
NOTE:    Variable Name Change.  COMMENTS (for visits) -> COMMENTS__for_visits_

NOTE: The import data set has 8 observations and 4 variables.
NOTE: There were 8 observations read from the data set X.'VISIT, TPT CODELISTS'n.
NOTE: The data set WORK.VISIT__TPT_CODELISTS has 8 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds


NOTE: %INCLUDE (level 1) ending.
SYMBOLGEN:  Macro variable OPTSAVE resolves to VALIDMEMNAME=EXTEND
866
867   options &optsave;

[Tom]

Looks like it worked to me:

NOTE: There were 24 observations read from the data set X.'DATASET VARIABLE ATTRIBUTES'n.
NOTE: The data set WORK.DATASET_VARIABLE_ATTRIBUTES has 24 observations and 11 variables.

[djbateman]

I'm not sure what to say.  The log is clean and seems fine, but nothing happened.  I still can't access datasets like the ones named "DATASET VARIABLE ATTRIBUTES" or "VISIT, TPT CODELISTS" because of the spaces and comma.

I pasted a screenshot of the CONTENTS table earlier, and it shows that MEMNAME still contains the spaces.  I'm not fully sure what your code was supposed to do, but I assumed it would translate spaces and special characters into underscores?

[Tom]

The code I posted was for COPYING the data from the spreadsheet into SAS dataset that use normal SAS member names.

So use the copy, not the original.

If you want to keep the datasets around so you can work on them later write them to a permanent libref instead of writing them to WORK.

If you want to work with the existing names then leave the VALIDMEMNAME option set to EXTEND and use the name literals when referencing the dataset.

[djbateman]

Okay, I see!  It did work.  I was multitasking, so the datasets got mingled among others that I was working with.  I didn't realize fully what it had done until I started with a clean slate.  I do see now.  Thanks for your help!

[Tom]

https://communities.sas.com/t5/SAS-Programming/LIBNAME-and-xlsx/m-p/676280/highlight/true#M203903

[Tom]

Do you want to also add logic to fix the variable (aka COLUMN) names?

NOTE:    Variable Name Change.  Req, Exp, Perm? -> VAR8