BookmarkSubscribeRSS Feed
Actuarius
Calcite | Level 5
I have a comma delimited data file that has one column that contains an unwanted comma in the form: lastname, firstname. The source data does not have quotation marks around each entry. Does anybody know how to rid this data of the unwanted comma, while still keeping the rest of the commas as the delimiters?
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Use the FIND function in a DATA step (pre-process your file with ) with a negative value for the second argument. But what if there isn't a comma present?

Or how about inputting two variables LNAME FNAME (along with the MISSOVER option on the INFILE stmt) and then you may or may not need to concatenate the two variables to reconstruct your FULL_NAME variable.

Scott Barry
SBBWorks, Inc.
Robert_Bardos
Fluorite | Level 6
Without sample data, I'd say there is no fool-proof answer to your question.

An approach however that comes to mind:
a) do an "input @;" then count the commas in _infile_
b) do an "input var1 var2 var3 @;" and stop just ahead of the variable that may contain the unwanted comma
c) do the next input statement according to the number of commas counted (ie. read one or two fields) then @ stop again
d) do an input for the remaining variables

This might work if what you described is the only oddity that occurs in the data. Reality however is often far more complex, so take this advice with the proverbial grain of salt.

Another idea (much in Scott Barry's vein): if the immediately succeeding field (with respect to the field in question) is easily identifiable (by being either a constant or numeric value) then the SCAN function might come in handy.

Regards
Robert
Peter_C
Rhodochrosite | Level 12
too much trouble to get rid of it when compared with the ease of re-combining them once loaded separately:[pre] name = cats( lastName, ',' , firstName ) ;[/pre]
but that is just my opinion
peterC

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 703 views
  • 0 likes
  • 4 in conversation