How to import multiline CSV in SAS

Question

I got a file in this format.

abc;def;"ghi
asdasd
asdasd
asd
asd
aas
d
"

Now I want to import it with SAS. How do I handle the multiline values?

Answer 1

Hi Martin

The answer might depend on what causes the linefeeds to be there, what kind of linefeeds they are, and possibly also on the OS you're running SAS on as well as the version of SAS you're using. Not knowing any of the answers to these questions, here are a couple of suggestions:

First, you could try this infile statement on your data step:

infile "C:\test.csv" dsd delimiter=';' termstr=crlf;

the termstr=crlf tells SAS to only use Windows linefeeds to trigger new records.

Alternatively, you could have SAS pre-process your file byte by byte to ensure that any linefeeds within paired quotes are replaced (perhaps with spaces):

data _null_;
  infile 'C:\test.csv' recfm=n;
  file 'C:\testFixed.csv' recfm=n;
  input a $char1.;
  retain open 0;
  if a='"' then open=not open;
  if (a='0A'x or a='0D'x) and open then put '00'x @;
  else put a $char1. @;
run;

This is adapted from here for your reference. You might need to tinker around with this code a bit to get it working. The idea is that you would then read the resulting csv into SAS with a standard data step.