Reading a CSV file in SAS

Stat/transfer is the easy way to transfer Excel .csv files when it works and is handy, but SAS can read a CSV file if you know the tricks.
  1. The file will probably have DOS line-endings, so specify the delimiter as both the comma and the carriage return. The way the do this is to write them in hexadecimal, as "DLM='2C0D'x" on the infile statement. (Bad MS and SAS).

  2. Specify infile option "dsd" so that consequtive commas are recognized as missing values. (Bad SAS).

  3. Specify infile option "missover" so that trailing missing values on a line are recognized as such (Excel doesn't output consequtive commas for trailling missing values). (Bad SAS).

  4. Specify infile option "lrecl" long enough for the longest record (it defaults to 256). (Bad MS and SAS).

  5. Specify infile option "firstobs=2" so that the line of column names is skipped. (Understandable).

Example

infile "file.csv" dlm='2C0D'x dsd missover lrecl=10000 firstobs=2;
input a b c d;

where '2C' is hexadecimal for decimal 44 which represents ',' , and '0D' is hexadecimal for decimal 13 which represents '\r'

It isn't at all clear why the first 4 items are not defaults on the infile statement, at least for .csv files - they are in many programming languages.

I thank Jean Roth for these solutions.

Since this was written SAS has issued a 13 page tech note which has much worthwhile information, but doesn't say if dlm is limited to a single character.

Daniel Feenberg
November 2008 April 2012