Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

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

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.

Attachment

Accepted Solutions
Solution
‎02-01-2018 10:34 AM
Super User
Super User
Posts: 8,073

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


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.

View solution in original post


All Replies
Contributor
Posts: 29

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

Posted in reply to paulkaefer
try something like this at the top of your code:

options validvarname=v7;
Regular Contributor
Posts: 215

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

Posted in reply to utrocketengineer

Thanks for the quick response @utrocketengineer. 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.

Super User
Posts: 13,508

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

Posted in reply to paulkaefer

paulkaefer wrote:

Thanks for the quick response @utrocketengineer. 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..

Regular Contributor
Posts: 215

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

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

Super User
Posts: 13,508

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

Posted in reply to paulkaefer

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.

Solution
‎02-01-2018 10:34 AM
Super User
Super User
Posts: 8,073

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


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.

Super User
Posts: 10,766

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

Posted in reply to paulkaefer
data _null_;
x=htmldecode("S&P 500");
put x=;
run;
Regular Contributor
Posts: 215

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

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.

Regular Contributor
Posts: 215

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

Posted in reply to 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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 216 views
  • 1 like
  • 5 in conversation