DATA Step, Macro, Functions and more

Clarification on file delimiter when there is a extra comma

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Clarification on file delimiter when there is a extra comma

Hi All,

 

I'm trying to write a SAS which is 'Comma' separated file and not properly alligned. In third & fifith records (or lines) there is a extra comma in the City (Ma,dison & flo,rida).  When i execute the below code, under 'GENDER'  city name 'dison' & 'rida' values getting updated.

 

Can anyone please help me how to write the code to get the correct values under correct column?

 

 

data address2;
infile datalines dsd;
length city $10;
input name $ age city $ gender $;
datalines;
Steve,32,Monona,M
Tom,44,Milwaukee,M
Deb,23,Ma,dison,F
bob,24,Texas,M
Harry,43,flo,rida,M
run;


Accepted Solutions
Solution
‎07-03-2017 05:16 AM
New Contributor
Posts: 4

Re: Clarification on file delimiter when there is a extra comma

Posted in reply to andreas_lds

Thank You so much andreas_ids ! it worked!

 

Obs CITY NAME AGE GENDER POS LEN

1 MONONA STEVE 32 M . .
2 MILWAUKEE TOM 44 M . .
3 MADISON DEB 23 F 8 2
4 TEXAS BOB 24 M . .
5 FLORIDA HARRY 43 M 10 3

 

 

View solution in original post


All Replies
Super User
Posts: 5,437

Re: Clarification on file delimiter when there is a extra comma

Does your source data look like this?
If so (that values themselves contain the delimiter), either ask for a file with a different delimter, or have sll char values enclosed between " signs.
Or, have the source fix the obvious data quality issues...
Data never sleeps
New Contributor
Posts: 4

Re: Clarification on file delimiter when there is a extra comma

Thank You for your response. Let me try andreas_ids

Super User
Super User
Posts: 7,988

Re: Clarification on file delimiter when there is a extra comma

Per standard CSV definition, if a column of data contains the delimiter comma, then that whole column of data needs to be enclosed within enough quote marks to ensure the comma within the text is not recognised as a delimiter of the data.  Typically this only needs one quote each end:
Harry,43,"flo,rida",M

New Contributor
Posts: 4

Re: Clarification on file delimiter when there is a extra comma

Thanks for your response!
Super Contributor
Posts: 345

Re: Clarification on file delimiter when there is a extra comma

Following the advice provided by @LinusH and @RW9 is highly recommended.

 

If you can't get a fixed source file, try the following code:

data address2;
infile datalines dsd;
length city $10;
input name $ age city $ gender $;

if countc(_infile_, ',') > 3 then do;
   gender = scan(_infile_, 1, ',', 'b');
   call scan(_infile_, 3, pos, len, ',');
   city = compress(substr(_infile_, pos, findc(_infile_, ',', 'b')-pos), ',');
end;

datalines;
Steve,32,Monona,M
Tom,44,Milwaukee,M
Deb,23,Ma,dison,F
bob,24,Texas,M
Harry,43,flo,rida,M
run;
Solution
‎07-03-2017 05:16 AM
New Contributor
Posts: 4

Re: Clarification on file delimiter when there is a extra comma

Posted in reply to andreas_lds

Thank You so much andreas_ids ! it worked!

 

Obs CITY NAME AGE GENDER POS LEN

1 MONONA STEVE 32 M . .
2 MILWAUKEE TOM 44 M . .
3 MADISON DEB 23 F 8 2
4 TEXAS BOB 24 M . .
5 FLORIDA HARRY 43 M 10 3

 

 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 150 views
  • 2 likes
  • 4 in conversation