topic Re: proc import formatting "linked" cell with '&' character in SAS Procedures

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

paulkaefer — Wed, 31 Jan 2018 16:24:05 GMT

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.

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

utrocketeng — Wed, 31 Jan 2018 16:27:33 GMT

try something like this at the top of your code:

options validvarname=v7;

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

paulkaefer — Wed, 31 Jan 2018 16:34:36 GMT

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.

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

ballardw — Wed, 31 Jan 2018 16:50:39 GMT

@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..

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

paulkaefer — Wed, 31 Jan 2018 17:00:43 GMT

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 &.

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

ballardw — Wed, 31 Jan 2018 21:05:45 GMT

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.

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

Tom — Wed, 31 Jan 2018 21:18:56 GMT

@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.

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

Ksharp — Thu, 01 Feb 2018 13:20:52 GMT

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

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

paulkaefer — Thu, 01 Feb 2018 15:38:39 GMT

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

select tranwrd(A, "&amp;", "&") 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.

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

paulkaefer — Mon, 05 Feb 2018 17:58:20 GMT

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.