topic Re: Why is PROC SQL uppercasing from Excel? in SAS Procedures

Why is PROC SQL uppercasing from Excel?

gewing — Mon, 01 Jul 2019 21:53:30 GMT

%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'.

Re: Why is PROC SQL uppercasing from Excel?

Tom — Mon, 01 Jul 2019 21:56:54 GMT

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.

Re: Why is PROC SQL uppercasing from Excel?

gewing — Mon, 01 Jul 2019 21:58:21 GMT

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?

Re: Why is PROC SQL uppercasing from Excel?

Tom — Mon, 01 Jul 2019 21:59:06 GMT

What does PROC CONTENTS show you?

Re: Why is PROC SQL uppercasing from Excel?

gewing — Mon, 01 Jul 2019 22:03:16 GMT

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?

Re: Why is PROC SQL uppercasing from Excel?

Tom — Mon, 01 Jul 2019 22:06:51 GMT

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;

Re: Why is PROC SQL uppercasing from Excel?

gewing — Mon, 01 Jul 2019 22:27:07 GMT

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.

Re: Why is PROC SQL uppercasing from Excel?

Tom — Tue, 02 Jul 2019 00:00:52 GMT

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

Re: Why is PROC SQL uppercasing from Excel?

Reeza — Tue, 02 Jul 2019 00:22:24 GMT

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.

Re: Why is PROC SQL uppercasing from Excel?

Tom — Tue, 02 Jul 2019 01:15:01 GMT

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.

Re: Why is PROC SQL uppercasing from Excel?

Susan_M — Tue, 02 Jul 2019 14:11:53 GMT

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.

Re: Why is PROC SQL uppercasing from Excel?

Reeza — Tue, 02 Jul 2019 18:30:42 GMT

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&docsetVersion=9.4&locale=en

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'.

Re: Why is PROC SQL uppercasing from Excel?

Susan_M — Tue, 02 Jul 2019 18:35:55 GMT

Thanks!! preserve_tab_names=Yes does the trick!!