Proc Import - Issue Reading Symbols from Spreadsheet Generated by R

[SMcRitchie]

I am using proc import to read in an excel sheet generated by someone using R.   One of the columns on the spreadsheet contains both text and some basic math symbols.  I normally don’t have an issue reading in this type of data using proc import (SAS 9.4), but in this case  “>” is being read in as “>”.   When I look at the spreadsheet, it shows the “>” symbol.   Any suggestions?

[andreas_lds]

Usual rant: don't use excel to transfer data, but csv, so that you don't have to rely on proc import.

I didn't see the issue, when importing a xlsx-file created by excel. Maybe the problem is the way R creates the file.

[ballardw · Accepted solution]

---

@andreas_lds wrote:

Usual rant: don't use excel to transfer data, but csv, so that you don't have to rely on proc import.

I didn't see the issue, when importing a xlsx-file created by excel. Maybe the problem is the way R creates the file.

---

Likely from the choice of R package(s) creating some flavor of HTML output instead of plain text (ASCII characters) for that character. It may look something like a ">" character in display but is not but a similar "standard" so that the symbol renders properly regardless of the font used for display of HTML.

You can use the TRANWRD function in a data step to replace the now revealed "&gt" with simple ASCII ">"

[SMcRitchie]

Thank you @andreas_lds and @ballardw.   I asked the person who provided the excel sheet to provide me with a csv file and that solved the problem -- likely because as @ballardw said, the csv file is ASCII.  I will remember the TRANWRD function in case I encounter an excel sheet where I can't go back and ask the person to re-export.  

[ballardw]

Glad you could resolve this.

My particular related peeve is one of my data sources has what should be an apostrophe come in as "'" yes 5 characters. In the same data a different variable instead of apostrophe in what should be "Master's Degree" I can't even type as it includes a Euro sign and the Trademark symbol.

These values apparently come from a drop-down selection list in an application so are pretty consistent. I can write custom informats to fix these as I read these source files frequently.