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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Fathima
Fluorite | Level 6

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

6 REPLIES 6
LinusH
Tourmaline | Level 20
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
Fathima
Fluorite | Level 6

Thank You for your response. Let me try andreas_ids

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Fathima
Fluorite | Level 6
Thanks for your response!
andreas_lds
Jade | Level 19

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;
Fathima
Fluorite | Level 6

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

 

 

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
  • 791 views
  • 2 likes
  • 4 in conversation