DATA Step, Macro, Functions and more

raw file with commas and double quotes

Reply
Super Contributor
Posts: 279

raw file with commas and double quotes

I have a txt file to read in like so (headers are added by me):

 ID                  Last      First        Guardians                               Address

1968451595,"Smith","Dianne","Smith, Patty or Smith, David","90 Flyth Rd",

 

Here is my infile statement:

infile "G:\Departments\Research\test scores\GT\&yr.\GIFT.txt" dlm = '",' MISSOVER lrecl=32767 firstobs=2 ;

 

The problem I'm encountering is the guardian field > "Smith, Patty or Smith, David". Many of observations have commas that are meant to simply separate their last and first name but are not meant to be delimiters. This is causing my SAS data set to read invalid data for these observations.

Super User
Posts: 19,770

Re: raw file with commas and double quotes

infile "G:\Departments\Research\test scores\GT\&yr.\GIFT.txt" DSD truncover lrecl=32767 firstobs=2 ;

Try above. 

 

If you have embedded commas in a field, the correct convention is to quote them if the file is comma delimited, so someone actually created your file correctly Smiley Happy

Super Contributor
Posts: 279

Re: raw file with commas and double quotes

Ok, this was helpful. It read about 6K obs before hitting another snag.

 

19182,19182,0,3783484502,"Mays","Chad Ronald","Mays, Kay or Mays, Hank "Ch","13 Moonlight Dr",

 

It appears the problem starts at the guardian field again but this time because of quotes within the field, i.e. "Ch"

Super User
Posts: 11,343

Re: raw file with commas and double quotes

It really helps to provide the exact code you are using as we have no clue what your variable names are without.

 

You may either have to edit your file (easiest and quickest though a pain) or learn to write your own code to parse a line of text that will work. There is no easy quick fix for garbage data.

Note that this should read okay:

19182,19182,0,3783484502,"Mays","Chad Ronald","Mays, Kay or Mays, Hank Ch","13 Moonlight Dr"

 

You may want to go back to the data source and tell them that some one is entering "  characters into what appears to be (a poorly defined or structured) name field inappropriately.

PROC Star
Posts: 102

Re: raw file with commas and double quotes


GreggB wrote:

Ok, this was helpful. It read about 6K obs before hitting another snag.

 

19182,19182,0,3783484502,"Mays","Chad Ronald","Mays, Kay or Mays, Hank "Ch","13 Moonlight Dr",

 

It appears the problem starts at the guardian field again but this time because of quotes within the field, i.e. "Ch"



GreggB wrote:

Ok, this was helpful. It read about 6K obs before hitting another snag.

 

19182,19182,0,3783484502,"Mays","Chad Ronald","Mays, Kay or Mays, Hank "Ch","13 Moonlight Dr",

 

It appears the problem starts at the guardian field again but this time because of quotes within the field, i.e. "Ch"


There seems to be a problem with the program creating your .csv file. If possible, tell the guys who create the file that they should double double quotes (sic) in text fields, like in "Mays, Kay or Mays, Hank ""Ch".

 

Regards,

Søren

Ask a Question
Discussion stats
  • 4 replies
  • 204 views
  • 1 like
  • 4 in conversation