XLSX libname engine reads & as &?

[jtlbg]

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.

[Reeza]

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

[jtlbg]

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]

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]

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]

data new;
set x.'Sheet1$'n ;
run;

[jtlbg]

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]

This could work ?


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

[jtlbg]

@Ksharp - unfortunately not. Same issue.

[Ksharp]

Oh, I misunderstood.
Try this function.


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

run;