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;
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
Thank You for your response. Let me try andreas_ids given solution to resolve this issue.
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
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.