topic Re: XLSX libname engine reads & as &? in SAS Programming

XLSX libname engine reads & as &?

jtlbg — Wed, 12 Apr 2017 11:39:41 GMT

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.

Re: XLSX libname engine reads & as &?

Reeza — Wed, 12 Apr 2017 11:47:22 GMT

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

Re: XLSX libname engine reads & as &?

jtlbg — Wed, 12 Apr 2017 11:50:29 GMT

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.

Re: XLSX libname engine reads & as &?

Patrick — Wed, 12 Apr 2017 11:51:16 GMT

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 &AMP; encoding happens when you read the sheet and doesn't already get written this way to the sheet in an earlier step?

Re: XLSX libname engine reads & as &?

jtlbg — Wed, 12 Apr 2017 12:05:26 GMT

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.

Re: XLSX libname engine reads & as &?

Ksharp — Wed, 12 Apr 2017 12:35:42 GMT


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

Re: XLSX libname engine reads & as &?

jtlbg — Wed, 12 Apr 2017 12:41:12 GMT

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.

Re: XLSX libname engine reads & as &?

Ksharp — Wed, 12 Apr 2017 12:45:29 GMT

This could work ?


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

Re: XLSX libname engine reads & as &?

jtlbg — Wed, 12 Apr 2017 12:49:01 GMT

@Ksharp - unfortunately not. Same issue.

Re: XLSX libname engine reads & as &?

Ksharp — Wed, 12 Apr 2017 12:52:47 GMT

Oh, I misunderstood.
Try this function.


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

run;

topic Re: XLSX libname engine reads & as &amp;? in SAS Programming

XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

Re: XLSX libname engine reads & as &?

topic Re: XLSX libname engine reads & as &? in SAS Programming