Reading a CSV file in SAS

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.

Daniel Feenberg
November 2008