BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gewing
Fluorite | Level 6
%macro getNames (ssheet,sasds);
libname X xlsx "pathpathpath/archive/extracted/20190627/&ssheet";
proc sql noprint;
	create table sheets_&sasds. as 
		select memname 
			from dictionary.members
				where libname="X";
quit;

%mend;

data _null_;
	set download_file_usablelist;
	practice_prefix=substr(file_name,1,8);
	call execute('%getNames(ssheet='!!file_name!!',sasds='!!practice_prefix!!')');
run;

Hi SAS friends!

 

I'm working on reading sheet names from 500+ Excel workbooks using PROC SQL. The code below works great, BUT the resulting observations in data sets 'sheets_&sasds.' are all automatically upcased. I'm using the SAS grid, so I need these to be in the case they were originally in, because I'm intending to turn these sheet names into a macro variable to be used in PROC IMPORT so I can pull the data from specific sheets in the workbooks. Any suggestions to make this stop the automatic capitalization?

 

Disclaimer: Full path not included here, replaced with 'pathpathpath'.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Try using the PRESERVE_TAB_NAMES option on the LIBNAME statement to keep the case as desired. 

 

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1qsjn9a18pja2n1gdctb7mc1ik8.htm&docset...

 

libname X xlsx "pathpathpath/archive/extracted/20190627/&ssheet" preserve_tab_names=Yes;

@gewing wrote:
%macro getNames (ssheet,sasds);
libname X xlsx "pathpathpath/archive/extracted/20190627/&ssheet";
proc sql noprint;
	create table sheets_&sasds. as 
		select memname 
			from dictionary.members
				where libname="X";
quit;

%mend;

data _null_;
	set download_file_usablelist;
	practice_prefix=substr(file_name,1,8);
	call execute('%getNames(ssheet='!!file_name!!',sasds='!!practice_prefix!!')');
run;

Hi SAS friends!

 

I'm working on reading sheet names from 500+ Excel workbooks using PROC SQL. The code below works great, BUT the resulting observations in data sets 'sheets_&sasds.' are all automatically upcased. I'm using the SAS grid, so I need these to be in the case they were originally in, because I'm intending to turn these sheet names into a macro variable to be used in PROC IMPORT so I can pull the data from specific sheets in the workbooks. Any suggestions to make this stop the automatic capitalization?

 

Disclaimer: Full path not included here, replaced with 'pathpathpath'.

 


 

View solution in original post

12 REPLIES 12
Tom
Super User Tom
Super User

Can you explain what you are talking about?

If SAS is changing the values of cells in the Excel sheets please show an example.

 

Or are you just talking about the LIBNAME or MEMNAME field from DICTIONARY.MEMBERS? That view always shows those names in uppercase.  If you don't want that then don't use that method to get the metadata.

gewing
Fluorite | Level 6

Well, I was talking about the latter since that's what my code is doing... is there a way to maintain the original case of the sheet name?

Tom
Super User Tom
Super User

What does PROC CONTENTS show you?

gewing
Fluorite | Level 6

The PROC CONTENTS output is moot. I'm trying to create datasets that correspond to each of the 500+ workbooks I have, that contain the names of all of the worksheets in the Excel workbook. The code I have does that, but I'm wondering if there's a way to maintain the original case. Since I want to take those values to feed into PROC IMPORT on SAS grid, I need the case of the variable to match the case of the Excel sheet. Is there a better way to do this that would maintain the capitalization that isn't the PROC SQL dictionary code?

Tom
Super User Tom
Super User

Not sure why you think using DICTIONARY views are any better than using PROC CONTENTS.

 

%macro getNames (ssheet,sasds);
libname X xlsx "pathpathpath/archive/extracted/20190627/&ssheet";
proc contents data=x._all_ noprint out=contents_&sasds; 
run;
proc sort data=contents_&sasds(keep=memname) nodupkey
  out=sheets_&sasds
;
run;
%mend;
gewing
Fluorite | Level 6

Mainly because I had no idea PROC CONTENTS could do that with Excel! Very cool. However when I run that, it also uppercases the MEMNAME like the DICTIONARY views, and there aren't any observations in the sheets_&sasds. datasets.

 

 

 

 

Tom
Super User Tom
Super User

So then it looks like it is the XLSX engine that is upcasing the names it generates.

 

You can just read the list of sheets from the XML that makes up the XLSX file.

%let filename="c:\downloads\testa.xlsx";
%let sheets=my_sheets;

*----------------------------------------------------------------------;
* Generate XMLMAP to read the sheetnames from xl/workbook.xml ;
*----------------------------------------------------------------------;
filename _wbmap temp;
data _null_;
  file _wbmap;
  put '<SXLEMAP version="2.1"><TABLE name="Sheets">'
    / '<TABLE-PATH>/workbook/sheets/sheet</TABLE-PATH>'
    / '<COLUMN name="Sheet"><TYPE>character</TYPE>'
    / '<DESCRIPTION>Sheet Name</DESCRIPTION>'
    / '<PATH>/workbook/sheets/sheet/@name</PATH>'
    / '<DATATYPE>string</DATATYPE><LENGTH>32</LENGTH>'
    / '</COLUMN>'
    / '<COLUMN name="State"><TYPE>character</TYPE>'
    / '<DESCRIPTION>Sheet State</DESCRIPTION>'
    / '<PATH>/workbook/sheets/sheet/@state</PATH>'
    / '<DATATYPE>string</DATATYPE><LENGTH>20</LENGTH>'
    / '</COLUMN>'
    / '</TABLE></SXLEMAP>'
  ;
run;

*----------------------------------------------------------------------;
* Copy xl/workbook.xml from XLSX file to physical file ;
* Note: Cannot use ZIP filename engine with XMLV2 libname engine ;
*----------------------------------------------------------------------;
filename _wbzip ZIP &filename member='xl/workbook.xml';
filename _wb temp ;
data _null_;
  infile _wbzip lrecl=30000;
  file _wb lrecl=30000;
  input;
  put _infile_;
run;

*----------------------------------------------------------------------;
* Generate LIBNAME pointing to copy of xl/workbook.xml from XLSX file ;
*----------------------------------------------------------------------;
libname _wb xmlv2 xmlmap=_wbmap ;

*----------------------------------------------------------------------;
* Read sheet names from XLSX file into a SAS dataset. ;
* Create valid SAS dataset name from sheetname or sheetnumber. ;
*----------------------------------------------------------------------;
filename extract temp;
data &sheets ;
  if eof then call symputx('nsheets',_n_-1);
  length Number 8;
  set _wb.sheets end=eof;
  number+1;
  length Memname $32 Filename $256 ;
  label number='Sheet Number' memname='Mapped SAS Memname' filename='Source Filename' ;
  filename = &filename ;
  if ^nvalid(compress(sheet),'v7') then memname = cats('Sheet',number);
  else memname = translate(trim(compbl(sheet)),'_',' ');
run;

proc print;
run;
Obs    Number    Sheet     State    Memname           Filename

 1        1      Sheet1             Sheet1     c:\downloads\testa.xlsx
 2        2      Sheet2             Sheet2     c:\downloads\testa.xlsx
 3        3      Sheet3             Sheet3     c:\downloads\testa.xlsx
Reeza
Super User

Since I want to take those values to feed into PROC IMPORT on SAS grid, I need the case of the variable to match the case of the Excel sheet. Is there a better way to do this that would maintain the capitalization that isn't the PROC SQL dictionary code?

 

SAS on Grid is case sensitive to the sheet names? Usually it's not case sensitive at all, except for character values entirely. Will have to test it on on regular SAS to see if that's an issue as well.

Tom
Super User Tom
Super User

Right. On Unix the case of FILENAMEs is case sensitive.

But within an XLSX sheet names are NOT case sensitive.  You cannot have one sheet named Sheet1 and a different sheet name SHEET1.

Susan_M
Calcite | Level 5
Yes, it will read in the upper case worksheet names, but if one needs to write out and you have picky end users it would be good to retain the case sensitivity.
Reeza
Super User

Try using the PRESERVE_TAB_NAMES option on the LIBNAME statement to keep the case as desired. 

 

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1qsjn9a18pja2n1gdctb7mc1ik8.htm&docset...

 

libname X xlsx "pathpathpath/archive/extracted/20190627/&ssheet" preserve_tab_names=Yes;

@gewing wrote:
%macro getNames (ssheet,sasds);
libname X xlsx "pathpathpath/archive/extracted/20190627/&ssheet";
proc sql noprint;
	create table sheets_&sasds. as 
		select memname 
			from dictionary.members
				where libname="X";
quit;

%mend;

data _null_;
	set download_file_usablelist;
	practice_prefix=substr(file_name,1,8);
	call execute('%getNames(ssheet='!!file_name!!',sasds='!!practice_prefix!!')');
run;

Hi SAS friends!

 

I'm working on reading sheet names from 500+ Excel workbooks using PROC SQL. The code below works great, BUT the resulting observations in data sets 'sheets_&sasds.' are all automatically upcased. I'm using the SAS grid, so I need these to be in the case they were originally in, because I'm intending to turn these sheet names into a macro variable to be used in PROC IMPORT so I can pull the data from specific sheets in the workbooks. Any suggestions to make this stop the automatic capitalization?

 

Disclaimer: Full path not included here, replaced with 'pathpathpath'.

 


 

Susan_M
Calcite | Level 5
Thanks!! preserve_tab_names=Yes does the trick!!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 1731 views
  • 6 likes
  • 4 in conversation