DATA Step, Macro, Functions and more

Special character in CSV causes SAS to skip over comma separator

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Special character in CSV causes SAS to skip over comma separator

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_address1bill_address2bill_address3bill_address4bill_address5
CSGravÚ90050 Honfleur 9403

 

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

Capture.PNG

 

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;

Accepted Solutions
Solution
‎02-20-2018 04:33 PM
Super User
Posts: 23,224

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

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

Capture.PNG

 

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;

 

View solution in original post


All Replies
PROC Star
Posts: 503

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

try dlm =','

New Contributor
Posts: 2

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

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

Solution
‎02-20-2018 04:33 PM
Super User
Posts: 23,224

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

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

Capture.PNG

 

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;

 

Respected Advisor
Posts: 4,665

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

@tluoskr

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

Capture.JPG

 

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.

Super User
Posts: 10,679

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

Maybe you need add ENCODING= option.

 

infile "[[filepath]]\test.csv" encoding='utf8' ..............
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 166 views
  • 2 likes
  • 5 in conversation