topic Re: Reading UTF-8 Encoded csv File in SAS Programming

Reading UTF-8 Encoded csv File

Satish_Parida — Tue, 20 Feb 2018 16:04:58 GMT

Hi All,

I am trying to read a utf-8 encoded csv file.

In Notepad++ the file looks fine.

In notepad the whole file comes as 1 line.

I am using a UNIX OS, where SAS 9.4 is installed.

So when SAS reads the file, it is only reading 1 record. As I am using firstobs=2 to avoid reading header, the final dataset contains 0 records.

I am using the following code.

filename file1 '/u04/dataloader/Satish/App/ZZ/f1.csv' encoding='utf-8';

data xxx.asdf;
infile file1 dsd dlm=',' firstobs=2 encoding='utf-8';
input SiteNumber:$10. LabName:$30. AnalyteName:$16. FromDate:$20. ToDate:$20. 
      LowRange:best12. HighRange:best12. Units:$10. Dictionary:$10. Comments$10.;
run;

I am trying to read the data from line2.

Thanks in advance.

Re: Reading UTF-8 Encoded csv File

GinaRepole — Tue, 20 Feb 2018 16:00:01 GMT

It sounds to me like the CSV file you're working with has Unix based line endings instead of Windows based line endings, so the records are not being properly split.

I was able to resolve this issue by quickly opening the file in WordPad (instead of Notepad) and saving it under a different name. It automatically corrected the line breaks.

Re: Reading UTF-8 Encoded csv File

Satish_Parida — Tue, 20 Feb 2018 16:04:28 GMT

@GinaRepole

Can it be done in bash command, or using any utility in base SAS?
I am trying to automate a batch process. I am using a UNIX OS, where SAS 9.4 is installed.
This is one example file.

Re: Reading UTF-8 Encoded csv File

GinaRepole — Tue, 20 Feb 2018 16:27:38 GMT

Oh, I see! If everything about your SAS session is Unix centralized, you should keep it that way. Just add the TERMSTR=LR option onto the INFILE statement and it should read the existing file correctly (or TERMSTR=CR if you may be missing carriage returns).

Re: Reading UTF-8 Encoded csv File

Satish_Parida — Tue, 20 Feb 2018 16:25:59 GMT

@GinaRepole

Thank you very much.

I was missing the carriage return.

filename file1 '/u04/dataloader/Satish/App/ZZ/f1.csv' encoding='utf-8';

data xxx.asdf;
infile file1 dlm=',' dsd firstobs=2 encoding='utf-8' TERMSTR=CR; /*CR Worked as carriage return*/
input SiteNumber:$10. LabName:$30. AnalyteName:$16. FromDate:$20. ToDate:$20. 
      LowRange:best12. HighRange:best12. Units:$10. Dictionary:$10. Comments$10.;
run;

Thank you very much

Re: Reading UTF-8 Encoded csv File

GinaRepole — Tue, 20 Feb 2018 16:28:49 GMT

Cheers 🙂

I adjusted my prior post to mention carriage returns as well in case anyone comes by to reference this in the future and only reads the solution post.

Re: Reading UTF-8 Encoded csv File

Tom — Tue, 20 Feb 2018 16:57:05 GMT

@Satish_Parida wrote:

@GinaRepole

Thank you very much.

I was missing the carriage return.

filename file1 '/u04/dataloader/Satish/App/ZZ/f1.csv' encoding='utf-8';

data xxx.asdf;
infile file1 dlm=',' dsd firstobs=2 encoding='utf-8' TERMSTR=CR; /*CR Worked as carriage return*/
input SiteNumber:$10. LabName:$30. AnalyteName:$16. FromDate:$20. ToDate:$20. 
      LowRange:best12. HighRange:best12. Units:$10. Dictionary:$10. Comments$10.;
run;

Thank you very much

Note: As far as I can tell only Microsoft Excel on Mac is still creating files using CR only as the end of line marks. But you can pick a different file format when you save the files from Excel so that it will generate files using LF or CR+LF instead.

Re: Reading UTF-8 Encoded csv File

Satish_Parida — Wed, 21 Feb 2018 11:54:55 GMT

If the source of the file is different and we can not predict the line breaks

Can we use more than one TERMSTR option in one data step. like LF+CR