topic Re: Special character in CSV causes SAS to skip over comma separator in SAS Programming

Special character in CSV causes SAS to skip over comma separator

tluoskr — Tue, 20 Feb 2018 20:07:44 GMT

I'm importing a CSV file into SAS using INFILE / INPUT, but a special character is causing SAS to skip over the comma delimiter. Previous posts have suggested that I use SAS with Unicode Support, but I still run into the problem. I'm using SAS 9.4 with Unicode Support. My data looks something like this:

bill_address1	bill_address2	bill_address3	bill_address4	bill_address5
CS	GravÚ	90050	Honfleur	9403

But when I import the data with my code, SAS ignores the comma separator between bill_address2 and bill_address3:

The log file didn't output any errors or warnings. My code is below (replacing "[[filepath]]" with the data location)

data test;
	infile "[[filepath]]\test.csv"
	dsd missover lrecl = 32767 firstobs = 2;
	informat
		bill_address1 $2.
		bill_address2 $12.
		bill_address3 $12.
		bill_address4 $12.
		bill_address5 $12.
	;
	input
		bill_address1 $
		bill_address2 $
		bill_address3 $
		bill_address4 $
		bill_address5 $
	;
run;

Re: Special character in CSV causes SAS to skip over comma separator

kiranv_ — Tue, 20 Feb 2018 20:29:14 GMT

try dlm =','

Re: Special character in CSV causes SAS to skip over comma separator

tluoskr — Tue, 20 Feb 2018 20:33:27 GMT

I tried replacing dsd with dlm = ",", but I still have the same problem.

Re: Special character in CSV causes SAS to skip over comma separator

Reeza — Tue, 20 Feb 2018 20:38:09 GMT

@kiranv_ DSD implies DLM.

@tluoskr View your file with a text editor (hex format) to determine what that special character is, once you know what the character is, you can strip it out with TRANSLATE or COMPRESS. I suspect it's a carriage return, especially if your data was in Excel at some point and someone had formatted the data using ALT+ENTER. If that's the case there are two solutions from the past week on this question posted on the forum.

@tluoskr wrote:

I'm importing a CSV file into SAS using INFILE / INPUT, but a special character is causing SAS to skip over the comma delimiter. Previous posts have suggested that I use SAS with Unicode Support, but I still run into the problem. I'm using SAS 9.4 with Unicode Support. My data looks something like this:

bill_address1 bill_address2 bill_address3 bill_address4 bill_address5

CS GravÚ 90050 Honfleur 9403

But when I import the data with my code, SAS ignores the comma separator between bill_address2 and bill_address3:

The log file didn't output any errors or warnings. My code is below (replacing "[[filepath]]" with the data location)
data test;
	infile "[[filepath]]\test.csv"
	dsd missover lrecl = 32767 firstobs = 2;
	informat
		bill_address1 $2.
		bill_address2 $12.
		bill_address3 $12.
		bill_address4 $12.
		bill_address5 $12.
	;
	input
		bill_address1 $
		bill_address2 $
		bill_address3 $
		bill_address4 $
		bill_address5 $
	;
run;

Re: Special character in CSV causes SAS to skip over comma separator

Patrick — Tue, 20 Feb 2018 21:31:43 GMT

@tluoskr

Using the data and code you've posted I can't replicate the issue you describe but get the desired result.

Can you please try and post some data which allows us to replicate the issue.

As already suggested by others: Use a text editor like Notepad++ which allows you to make all characters visible and search for something unusual in the data.

Re: Special character in CSV causes SAS to skip over comma separator

Ksharp — Thu, 22 Feb 2018 06:11:10 GMT

Maybe you need add ENCODING= option.

infile "[[filepath]]\test.csv" encoding='utf8' ..............

Re: Special character in CSV causes SAS to skip over comma separator

Na_vin — Thu, 21 Feb 2019 16:32:15 GMT

You are right the encoding option works.

encoding in the infile statement should match the source(where the csv is prepared) encoding.

target SAS environment has to be set to UTF8.

wlatin1 is one of the encodings used by WIndows systems.

example:

infile "[[filepath]]\test.csv" encoding='wlatin1'