topic Re: raw file with commas and double quotes in SAS Programming

raw file with commas and double quotes

GreggB — Wed, 17 May 2017 19:45:58 GMT

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.

Re: raw file with commas and double quotes

Reeza — Wed, 17 May 2017 19:53:34 GMT

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 🙂

Re: raw file with commas and double quotes

GreggB — Wed, 17 May 2017 20:03:34 GMT

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"

Re: raw file with commas and double quotes

ballardw — Wed, 17 May 2017 20:57:39 GMT

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.

Re: raw file with commas and double quotes

s_lassen — Thu, 18 May 2017 08:14:11 GMT

@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