Date:         Tue, 31 Jan 2006 13:26:39 -0800
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      Re: SAS Import CSV Parsing Error
Comments: To: jamesgreen55@YAHOO.CA
Content-Type: text/plain; charset="us-ascii"

James -

I can't replicate your problem. As usual, Peter is correct that you
don't need the dlm=','.  But I wonder if you have unexpected LF values
in your file.  Have you opened your CSV in a text reader and inspected
it?

You can use this to inspect your file in hex using SAS:
data _null_;
    infile '\\server\sas\test_environment\pts_7x50_tlwebtool.csv'
           recfm=n;
    input byte $char1. @;
    position+1;
    hexbyte=put(byte,$hex2.);
    put (_all_) (= );
run;

Look for the LF value '0A'x in your comment fields where SAS is breaking
the lines.

The other hex values are at: http://www.lookuptables.com/

This removes all '0A'x LF characters except when in a CRLF pair:

data _null_;
    retain flag;
     infile '\\server\sas\test_environment\pts_7x50_tlwebtool.csv'
            recfm=n;
    input byte $char1. @;
    if byte='0A'x and flag='n' then byte=''x;
    file ''\\server\sas\test_environment\fixed_pts_7x50_tlwebtool.csv'
            recfm=n;
    put byte $char1. @;
    if byte='0D'x then flag='y'; else flag='n';
run;

hth

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
jamesgreen55@YAHOO.CA
Sent: Tuesday, January 31, 2006 11:13 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS Import CSV Parsing Error

Hello SAS-L; SAS Ver9.x WinXP.

A bit of a dilema. I am forced to import a CSV file which originates
from a 3rd party s/w tool. Funny thing is that when I import the CSV
into EXCEL it parses fine and seems to ignore commas in the free text
comment fields. (Root cause).

SAS however; starts a new column when it sees the comma in the comment
fields, so I end up with 80% Good data and ~20% Bad data. (I'll get
peoples names in the company var, titles in the product names etc..).

So I have two questions, why do you think Excel can load the file
without problems? And why is it that SAS cannot? (partial code below);

Is there an option I can add?

data WORK.TEST                               ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile '\\server\sas\test_environment\pts_7x50_tlwebtool.csv'
    delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

       informat PTS_Number best32. ;
       informat Comments $50. ;
         format PTS_Number best12. ;
         format Comments  $50. ;
              input
                PTS_Number
                Comments  $
    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection
macro variable */
run;