topic Re: SAS import file with text qualifier in SAS Programming

SAS import file with text qualifier

mikezhang — Wed, 12 Aug 2015 15:05:40 GMT

"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,

Var1

Var2

Var3

Var4

Var5

Var6

Var7

Var8

Var9

Var10

Var11

Var12

Var13

Var14

Var15

Var16

Var17

Var18

Var19

Var20

Var21

Var22

Var23

Var24

Var25

Var26

Var27

Var28

Var29

Var30

6/23/2015 8:28

6/23/2015 7:31

PERSON0

PERSON1

LNAME, FNAME

AREA0

TYP_CD

CD_NEW

CD_R

DISPOSTION

LNAME, FNAME

DATE

DATE1

TYPE1

TYPE2

AREA

AREA1

TYPE3

NAME

SERIES

SERIES2

TYPE5

Thank you again.

Re: SAS import file with text qualifier

Reeza — Wed, 12 Aug 2015 16:46:40 GMT

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

Re: SAS import file with text qualifier

mikezhang — Wed, 12 Aug 2015 16:56:29 GMT

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

Re: SAS import file with text qualifier

sbb — Wed, 12 Aug 2015 17:00:54 GMT

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.

Re: SAS import file with text qualifier

ballardw — Wed, 12 Aug 2015 19:02:52 GMT

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)"

",,,,"

Re: SAS import file with text qualifier

mikezhang — Wed, 12 Aug 2015 19:46:31 GMT

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.

Re: SAS import file with text qualifier

ballardw — Wed, 12 Aug 2015 20:37:36 GMT

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.