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.
- 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).
- Specify infile option "dsd" so that consequtive commas are recognized as missing values. (Bad SAS).
- 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).
- Specify infile option "lrecl" long enough for the longest record (it defaults to 256). (Bad MS and SAS).
- 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