DATA Step, Macro, Functions and more

XLSX Engine Name Literals in Windows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

XLSX Engine Name Literals in Windows

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


Accepted Solutions
Solution
a week ago
Super User
Posts: 3,857

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

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

View solution in original post


All Replies
Super User
Posts: 13,321

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

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.

Occasional Contributor
Posts: 19

Re: XLSX Engine Name Literals in Windows

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.

Super User
Posts: 23,295

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

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

Occasional Contributor
Posts: 19

Re: XLSX Engine Name Literals in Windows

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

Super User
Posts: 13,321

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

@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).

Esteemed Advisor
Posts: 5,482

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

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. 

PG
Occasional Contributor
Posts: 19

Re: XLSX Engine Name Literals in Windows

Agreed. Thanks.

Super User
Posts: 3,857

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

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";

Occasional Contributor
Posts: 19

Re: XLSX Engine Name Literals in Windows

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

Solution
a week ago
Super User
Posts: 3,857

Re: XLSX Engine Name Literals in Windows

Posted in reply to ericliuzh1

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 129 views
  • 3 likes
  • 5 in conversation