BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ericliuzh1
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

View solution in original post

10 REPLIES 10
ballardw
Super User

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
Obsidian | Level 7

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
Super User

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

ericliuzh1
Obsidian | Level 7

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

ballardw
Super User

@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
Opal | Level 21

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
ericliuzh1
Obsidian | Level 7

Agreed. Thanks.

SASKiwi
PROC Star

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
Obsidian | Level 7

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

SASKiwi
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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