DATA Step, Macro, Functions and more

XLSX libname engine reads & as &?

Reply
Occasional Contributor
Posts: 10

XLSX libname engine reads & as &?

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.

Super User
Posts: 17,840

Re: XLSX libname engine reads & as &?

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

Occasional Contributor
Posts: 10

Re: XLSX libname engine reads & as &?

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.
Respected Advisor
Posts: 3,894

Re: XLSX libname engine reads & as &?

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?

Occasional Contributor
Posts: 10

Re: XLSX libname engine reads & as &?

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.
Super User
Posts: 9,682

Re: XLSX libname engine reads & as &?


data new;
set x.'Sheet1$'n ;
run;
Occasional Contributor
Posts: 10

Re: XLSX libname engine reads & as &?

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.
Super User
Posts: 9,682

Re: XLSX libname engine reads & as &?

This could work ?


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

Occasional Contributor
Posts: 10

Re: XLSX libname engine reads & as &?

@Ksharp - unfortunately not. Same issue.
Super User
Posts: 9,682

Re: XLSX libname engine reads & as &?

Oh, I misunderstood.
Try this function.


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

run;


Ask a Question
Discussion stats
  • 9 replies
  • 145 views
  • 1 like
  • 4 in conversation