XLSX Engine Name Literals in Windows

[ericliuzh1]

On P125 of the Prep Guide 4th ed, it said "The Excel worksheet names have the special character ($) at the end... You must assign a name literal to the data set name." A follow-up example shows the Excel work sheet name is 'tests', so the name literal is 'tests$'n when running the DATA step, it's like

DATA STRESS; SET RESULTS.'tests$'n; RUN;

I used my local file to practice. I found that I can run the DATA step successfully without '$' at the end (still name literal like 'tests'n), but cannot make it with '$' at the end ('tests$'n). My Excel version is Excel2016.

If I get a question in the real test, how should I answer it? 

Thanks,

Eric

[ballardw]

My personal response would be convert the XLSX to CSV and control how the values appear using a data step to read the CSV.

Are you using an actual XLSX libname statement or a pcfiles libname? And did your xlsx file have more than one worksheet?

Between versions of SAS, versions of XLSX files (some of which are actually HTML, CSV or something else that people just name with xlsx extensions) and how "data" in Excel behaves because of things like multiple header rows I just plain don't trust any automatic conversion from Excel to SAS.

But then I'm a curmudgeon and learned SAS before Windows could run SAS.

[ericliuzh1]

I am using actual XLSX libname statement as the follows,

LIBNAME TEMP XLSX 'C:\...\FILENAME.XLSX';RUN;

the .xlsx file related to my question above has only one worksheet. I then created a 2-worksheet .xlsx file to try again. Still, SAS gives me error message when I used '$' at the end.

DATA TEMPS; SET ENRR.'sheet2$'n;run;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File ENRR.'sheet2$'n.DATA does not exist

But, it is okay when I do not use '$' at the end of the sheet name.

I am preparing for the Base programmer test, so what I care about is how I should answer the question when the Prep Guide said to use '$' at the end of the sheet name.

Thanks.

[Reeza]

I believe it's case sensitive as well, sheet2$ is not the same as Sheet2$.

[ericliuzh1]

Good points. But it seems not for this one. I tried 'SHeet1'n and 'sheet1'n. They all worked fine.

[ballardw]

---

@ericliuzh1 wrote:

I am using actual XLSX libname statement as the follows,

LIBNAME TEMP XLSX 'C:\...\FILENAME.XLSX';RUN;

the .xlsx file related to my question above has only one worksheet. I then created a 2-worksheet .xlsx file to try again. Still, SAS gives me error message when I used '$' at the end.

DATA TEMPS; SET ENRR.'sheet2$'n;run;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File ENRR.'sheet2$'n.DATA does not exist

But, it is okay when I do not use '$' at the end of the sheet name.

I am preparing for the Base programmer test, so what I care about is how I should answer the question when the Prep Guide said to use '$' at the end of the sheet name.

Thanks.

---

Likely not related to a "test" but in reality attempting anything along these lines without examining the XLSX file to see if any of the "automated" results will work first is poor planning. If your xlsx file has two or more header rows, or spanning headers and "data" not starting in the first column many of the Proc import/ excel/ xlsx engine approaches may not quite work as intended.

Also if the values are "stacked" such as age, height  and date of birth are all in the same column then you will have to parse the file anyway.

Not to mention the headaches arising when the column headers have more than 32 characters, include special characters and have the same text for the first 32 or more characters. You may have a "dataset" but variable names such as VAR33 VAR45 because the column headers for columns 33 and 45 were the same as some other variable for the first 32 characters in columns 10 and 23 (guess which is similar to which).

[PGStats]

In theory, when you use RESULTS.tests (or RESULTS.'tests'n) you should be accessing a named range called tests in your Excel workbook. A worksheet called tests should be accessed with RESULTS.'tests$'n. Note that it is possible to have both a worksheet and a named range called tests in the same workbook. In fact when you ask SAS to create a new table in an Excel workbook, it creates both the worksheet to contain the data and a named range to point to it.

At least, it was like that the last time I checked. 

[ericliuzh1]

Agreed. Thanks.

[SASKiwi]

If you assign a LIBNAME to an Excel workbook using the EXCEL engine rather than XLSX you will get dataset names with a $ suffix. Using the XLSX engine results in dataset names without the $ suffix:

libname MyExcel EXCEL "MyWorkbook.xlsx";

versus

libname MyExcel XLSX "MyWorkbook.xlsx";

[ericliuzh1]

Good to know. Thanks. But the EXCEL engine does not work on my desktop.

[SASKiwi · Accepted solution]

That might be due to the "bitness" problem - 64-bit SAS trying to read 32-bit MS Excel.