BookmarkSubscribeRSS Feed
jtlbg
Obsidian | Level 7

I have a .xlsx file which contains the string "X&Y" in a cell. I read it into SAS using the following code:

 

libname x xlsx "%sysfunc(pathname(work))\pvsds2.xlsx";
data new;
set x.'Sheet1'n ;
run;

 

In the dataset new, the value appears as "X&Y". Am I doing something wrong, or is the XLSX libname engine failing to properly handle the XML in which the XLSX file is encoded?

 

I am using Enterprise Guide 7.13 on SAS 9.4 on Linux.

9 REPLIES 9
Reeza
Super User

The underlying file is xml or XLSX? If it's XML use the XML engine not the XLSX. 

jtlbg
Obsidian | Level 7
The underlying file is XLSX. I only mention XML because I know that the XLSX file format is based on XML, and the issue looks very much like issues I have seen elsewhere with other software that does not correctly encode/decode/escape characters which are considered special in XML.
Patrick
Opal | Level 21

Interesting question.

 

You are reading the XLSX from your WORK directory so I assume you've created this XLSX with SAS. If so then how do you know that the & encoding happens when you read the sheet and doesn't already get written this way to the sheet in an earlier step?

jtlbg
Obsidian | Level 7
Hi Patrick, that's not the case. I'm uploading the file from my local PC, using the Copy Files task in EG. It was created in Excel 2010.

I should add that the value is not a literal "X&Y" string - the cell in question contains a VLOOKUP which returns the value "X&Y". SAS is correctly reading the literal value so the presence of the VLOOKUP seems relevant.
Ksharp
Super User

data new;
set x.'Sheet1$'n ;
run;
jtlbg
Obsidian | Level 7
I tried that syntax initially. I got this error:

ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File X.'Sheet1$'n.DATA does not exist.
Ksharp
Super User
This could work ?


libname x xlsx "%sysfunc(pathname(work))\pvsds2.xlsx";
proc copy in=x out=work noclone;
run;

jtlbg
Obsidian | Level 7
@Ksharp - unfortunately not. Same issue.
Ksharp
Super User
Oh, I misunderstood.
Try this function.


data x;
x='X&Y';
correct=htmldecode(x);

run;


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
  • 9 replies
  • 1699 views
  • 1 like
  • 4 in conversation