Trying to import a CSV file with double delimiters + carriage return

Question

I'm trying to import a CSV dataset using (the wizard in) SAS Enterprise Guide. It provides me with 2 options: 1) Text format as delimited fields or 2) Fixed columns

However, I have a dataset which consists of data with 2 delimiters(text between " and ,), and also a carriage return after each line of data. This means I can't make use of the wizard. However, due to the nature of the formats (double delimiter + carriage return), I'm wondering how to read this using 'regular' code.

Please find an example file (which I would like to import) here: https://www.briandunning.com/sample-data/us-500.zip.

Thank you in advance for your help!

user2877959 user2877959 · Accepted Answer · 2017-09-25T13:03:17

The issue here is likely that the import wizard doesn't figure out that the end of line symbol is a single carriage return. I'm not sure what you mean by "double delimiter". From what I can see in your example CSV, there is just a simple comma delimiter and values are enclosed in double quotes in order to mask commas in the values.

Based your sample CSV, this code imports it successfully:

data want;
length first_name $20
       last_name $20
       company_name $30
       address $50
       city $20
       county $20
       state $2
       zip $10
       phone1 $20
       phone2 $20
       email $100
       web $100
             ;
infile 'path-to-file\us-500.csv' dsd delimiter=',' termstr=cr missover firstobs=2;
input first_name $
      last_name $
      company_name $
      address $
      city $
      county $
      state $
      zip $
      phone1 $
      phone2 $
      email $
      web $;
run;

The key here is the termstr=cr option which tells the infile statement that the termination symbol is a carriage return. The dsd option states that values are enclosed in double quotes and that when a delimiter is found in between double quotes, it is to be treated as a character.

Of course you can play around with those length statements to best suit your actual data.

Trying to import a CSV file with double delimiters + carriage return

1 Answers