DATA Step, Macro, Functions and more

SAS import file with text qualifier

Reply
New Contributor
Posts: 3

SAS import file with text qualifier

"6/23/2015 8:55:36 AM,6/23/2015 7:57:55 AM,test,A B,""C, D "",E-MA,F,Personal G,G one,test - TWO THREE,""I LIKE APPLE"",""ONE, TWO"",FCB,6/27/2015 - 6/27/2016,6/23/2015,BIZ,Personal,MA,NY,Personal,Group,""NYC Ins. Companies"",,,""$NYC NY-MA, (Group)"",""$NYC NY-MA, (Group)"",,,,"

The data looks like this but with 10k rows. I was first trying to use PROC IMPORT but it didn't recognize the comma(,) as a delimiter in the file. Then I tried DATA STEP INFILE but still didn't work out the problem.

Does anyone have any experience importing files with text qualifiers?

Thank you.

-----------------------------------------------------------------------------------------------------------------

Update

I first attached .txt data and below is the .csv data.

Raw data,

6/23/2015 8:28:23 AM,6/23/2015 7:31:48 AM,PERSON0,PERSON1,"LNAME, FNAME ",DT,AREA0,TYP_CD,CD_NEW,CD_R,"DISPOSTION","LNAME, FNAME",DATE,DATE1,TYPE1,TYPE2,AREA,AREA1,TYPE3,NAME,NAME,,,SERIES,SERIES2,,TYPE5,,

Expected data,

Var1Var2Var3Var4Var5Var6Var7Var8Var9Var10Var11Var12Var13Var14Var15Var16Var17Var18Var19Var20Var21Var22Var23Var24Var25Var26Var27Var28Var29Var30
6/23/2015 8:286/23/2015 7:31PERSON0PERSON1LNAME, FNAME DTAREA0TYP_CDCD_NEWCD_RDISPOSTIONLNAME, FNAMEDATEDATE1TYPE1TYPE2AREAAREA1TYPE3NAMENAME SERIESSERIES2 TYPE5

Thank you again.

Super User
Posts: 17,809

Re: SAS import file with text qualifier

Did you specify DLM="," and dSD in your import code?

New Contributor
Posts: 3

Re: SAS import file with text qualifier

Yes, I specify the DLM=',' and DSD option.

Super User
Posts: 10,494

Re: SAS import file with text qualifier

The proliferation of two double=quotes  ( "") in that data tends to make SAS see each of these rows below as a single variable, when using DLM=',' and DSD

specifies that when data values are enclosed in quotation marks, delimiters

within the value are treated as character data

so NONE of your commas are seen as delimiters:

"6/23/2015 8:55:36 AM,6/23/2015 7:57:55 AM,test,A B,"

"C, D "

",E-MA,F,Personal G,G one,test - TWO THREE,"

"I LIKE APPLE"

","

"ONE, TWO"

",FCB,6/27/2015 - 6/27/2016,6/23/2015,BIZ,Personal,MA,NY,Personal,Group,"

"NYC Ins. Companies"

",,,"

"$NYC NY-MA, (Group)"

","

"$NYC NY-MA, (Group)"

",,,,"

New Contributor
Posts: 3

Re: SAS import file with text qualifier

The double quotes are text qualifiers and I guess my issue is how to deal with them.

The csv file is sort of different and it didn't treat comma as delimiter either.

Super User
Posts: 10,494

Re: SAS import file with text qualifier

I would expect a single double quote as a text identifier, which is also what SAS expects. Your very first value starts with a single double quote and starts looking for the next double quote to close the first variable, which occurs after a comma, so the comma cannot be treated as a delimiter. And since there is another " in what seems to be an inappropriate location that continues.

Also, examine your "csv" file with a text editor, not a spreadsheet. You may see things that a spreadsheet hides.

Super Contributor
Super Contributor
Posts: 3,174

Re: SAS import file with text qualifier

Suggest you might share what you would expect the resulting parsed, column-oriented data row to look like -- post an AFTER-parsing data representation. For example you have a quoted, character-type first column revealed, but that data-represented is not terminated with a closing double-quote, as far as I see with your pasted data.

Ask a Question
Discussion stats
  • 6 replies
  • 1558 views
  • 0 likes
  • 4 in conversation