BookmarkSubscribeRSS Feed
mikezhang
Calcite | Level 5

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

6 REPLIES 6
Reeza
Super User

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

mikezhang
Calcite | Level 5

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

ballardw
Super User

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

",,,,"

mikezhang
Calcite | Level 5

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.

ballardw
Super User

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.

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6380 views
  • 0 likes
  • 4 in conversation