topic Re: How to remove strange symbols from character data? in New SAS User

How to remove strange symbols from character data?

marmst10 — Fri, 30 Aug 2019 16:39:01 GMT

I am trying to import a .csv file which has the following format (first two observations)

PayeeID,Name,AreaID,RegionID,DistrictID,TerritoryID,TMRole,TMType,StartDate,EndDate
“0000100181”,"name",company name,30,321,3176,TM,TM,2018-01-01,2018-02-04

I use the following code to import:

proc import datafile = "C:\Users\conno\OneDrive\Desktop\DS 7900 - Data Science XLS Group\TEST.csv"
out = ds7900.data
DBMS = csv
replace;
run;

and the first column has some strange question mark symbols. For example, when printed to the results viewer:

Obs PayeeID12

�0000100181�

I want to remove these. Please help!

Re: How to remove strange symbols from character data?

JosvanderVelden — Fri, 30 Aug 2019 17:30:46 GMT

Probably because of the curly quotes of the first item cause the strange character. The straight quotes of the second item don't result in a strange character. Or do they?

Re: How to remove strange symbols from character data?

Reeza — Fri, 30 Aug 2019 17:59:09 GMT

You may have a different encoding on the file. Can you check what the encoding is of the file? This usually happens when you have files that have moved across OS (Unix to Windows) or working in different countries where a different encoding is standards...or manually fixing data and introducing 'smart quotes' (Google that) rather than traditional quotes. The smart quotes usually means the data was in Excel or Word at some point though the browser can also introduce those and mess up the text.

Re: How to remove strange symbols from character data?

Kurt_Bremser — Fri, 30 Aug 2019 18:08:56 GMT

You need to inquire how these curly quotes were created, they are not what should be used around values in csv files.

If you can't have it corrected at the source, you can change them by applying the translate() function on _infile_ before actually reading an observation:

input @;
_infile_ = translate(_infile_,'"','?');
input ........ ;

Replace the question mark with the curly quote you want replaced.

Re: How to remove strange symbols from character data?

marmst10 — Fri, 30 Aug 2019 18:13:27 GMT

This fixed it! Thank you. I just replaced the curly quotes, “ with straight quotes " and it removed the strange character.