BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tluoskr
Calcite | Level 5

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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

6 REPLIES 6
tluoskr
Calcite | Level 5

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

Reeza
Super User

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

 

Patrick
Opal | Level 21

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

Ksharp
Super User

Maybe you need add ENCODING= option.

 

infile "[[filepath]]\test.csv" encoding='utf8' ..............
Na_vin
Calcite | Level 5

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'

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6709 views
  • 4 likes
  • 6 in conversation