raw file with commas and double quotes

[GreggB]

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.

[Reeza]

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 🙂

[GreggB]

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"

[ballardw]

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.

[s_lassen]

---

@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