PC-SAS -- Problems with comma delimited input file (commas within a field)

[Luanne]

Hi!  I am using PC-SAS to read a comma delimited (csv) input file.  I am having a problems with fields not populating correctly due to a description field that contains multiple commas within the text.  On my input statement, I have identified ',' as the delimiter.  In some fields, it appears to be working okay.  However, everything after the first comma in the description field is populating the fields that follow.  I am trying to develop a standard process, where the data is extracted from an Oracle database to a .csv file, that is then processed by the SAS program.  I would like to avoid manually removing the commas from the description field.  Any suggestions?  Thanks!

[art297]

Couldn't you just extract a pipe (i.e., |) or tilde (~) or tab (or any character that doesn't exist in the file) delimited file?

[ballardw]

If you have to use CSV you may want to see if there is an option that produces quote qualified text. That would put quotes around the data that contains commas as part of the field and can be worked around.

[Tom]

Easiest is to SAS/Access to Oracle and pull directly from Oracle to SAS without the CSV file.

Otherwise get whoever generates the CSV file to be a little more sophisticated.  I think there are stored procedures available for Oracle that will properly quote data that contains the delimiter.  If not then you will need to see if you can find a delimiter that is not included in the data as Art suggested above.

[Tom]

If it is just the first line with the column headers then import it without names and with the FIRSTOBS=2 option to skip that line.

data want ;

   infile 'myfile.csv' dsd dlm=',' truncover firstobs=2;

   input col1-col20;

run;

[SASJedi]

Luanne,

If you have control of the Oracle export process, check out these instructions from the Oracle web site on how to get the output in standard CSV format (with quotes around the header values that are giving you so much grief) http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059

This process would be easier if you have SAS/Access to Oracle or SAS/Access to ODBC licensed and installed on your PC.  Then you could just attach to Oracle with a LIBNAME statement and use the Oracle tables just like SAS data sets.  You can check to see what you have licensed by submitting this code and reviewing the results in your log:

PROC SETININT NOALIAS;
RUN;

If you do have SAS/Access to Oracle licensed, but it's not working for you check out http://support.sas.com/techsup/technote/ts703.pdf for instructions on how to verify the installation and for sample code to access your Oracle tables with a LIBNAME statement.