topic Re: Proc Import - Issue Reading Symbols from Spreadsheet Generated by R in SAS Programming

Proc Import - Issue Reading Symbols from Spreadsheet Generated by R

SMcRitchie — Fri, 22 Apr 2022 12:13:11 GMT

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?

Re: Proc Import - Issue Reading Symbols from Spreadsheet Generated by R

andreas_lds — Fri, 22 Apr 2022 12:54:31 GMT

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.

Re: Proc Import - Issue Reading Symbols from Spreadsheet Generated by R

ballardw — Fri, 22 Apr 2022 14:56:13 GMT

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

Re: Proc Import - Issue Reading Symbols from Spreadsheet Generated by R

SMcRitchie — Fri, 22 Apr 2022 16:31:21 GMT

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.

Re: Proc Import - Issue Reading Symbols from Spreadsheet Generated by R

ballardw — Fri, 22 Apr 2022 16:56:08 GMT

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.