proc import formatting "linked" cell with '&' character

[paulkaefer]

See test code and Excel file, attached.

In sheet test1, cell A1 is "S&P 500" (without quotes). Proc import reads this fine.

In sheet test2, cell A1 links to another sheet, which has the value "S&P 500" (once again, no quotes in the cell itself).

Proc import reads this as "S&P 500". I recognize this as a character encoding for & used in HTML, etc.

Is there a way to force SAS to not do this? Some option for import?

Let me know if this would be more appropriate to post in the General SAS Programming forum.

[utrocketeng]

try something like this at the top of your code:

options validvarname=v7;

[paulkaefer]

Thanks for the quick response @utrocketeng. I'm still seeing & in this variable (note that I don't want this to be a variable name but a value). I tried with validvarname=any; and still am seeing & appear.

[ballardw]

---

@paulkaefer wrote:

Thanks for the quick response @utrocketeng. I'm still seeing & in this variable (note that I don't want this to be a variable name but a value). I tried with validvarname=any; and still am seeing & appear.

---

Things like &amp often indicate the file is actually HTML of some flavor and the source is lying to you by changing the file extension.

OR some other behavior such as copy and paste from a document in html format.

Do a file save as CSV for each sheet and import that..

[paulkaefer]

Thanks, @ballardw. Not an ideal solution, but that could work in a pinch.

We know the origin to be Excel. As my example shows, this only appears if you import a sheet where cells pull their value from another sheet. Both examples have the same human-readable value, but something in the import is converting one of the ampersands to &.

[ballardw]

Did you try the File Save As to CSV? I did with your example file and did not get the &amp when saving the Test2 tab to csv.

[Tom · Accepted solution]

---

@ballardw wrote:

Did you try the File Save As to CSV? I did with your example file and did not get the &amp when saving the Test2 tab to csv.

---

Since an CSV file by definition cannot contain any linked cells that would remove the symptom, but it is not a fix to reading the XLSX file directly.

The OP should report this behavior to SAS and see if they are planning any updates to fix it.

[Ksharp]

data _null_;
x=htmldecode("S&P 500");
put x=;
run;

[paulkaefer]

Thanks for the example of htmldecode, @Ksharp. Another option would be to do the following:

select tranwrd(A, "&", "&") as A_new

I submitted a track with tech support as @Tom suggests, and marked that comment as solution. I agree, the workarounds are not a fix for directly reading such an Excel file, but they can help in a pinch.

[paulkaefer]

I just wanted to loop back on this. As @Tom suggested, I opened a track with SAS tech support. The specialist responded that this has been fixed in the 9.4m5 release.