Removing embedded carriage returns

[Patrick]

@Tom

The OP uses a .csv and I've made the assumption that we're simply dealing the normal embedded LF problem but that a records ends with CRLF.

@Wmcfarland

Please post your sample.csv as attachment so we can deal with the real thing and stop with all the guesswork.

[AllanBowe]

Building on Tom's excellent solution, I built a macro to take ANY csv and "clean" it - by that I mean:

* Switch the file to CRLF line endings

* Switch embedded line breaks (within quotes) to LF

I believe this meets the OPs objective of reading it in, and "not changing any data" - as the line breaks are retained (albeit "fixed" to LF format)

To run:

/* compile macros */
filename mc url "https://raw.githubusercontent.com/macropeople/macrocore/master/macrocore.sas";
%inc mc;

/* run code */ 
%mp_cleancsv(in=old.csv,out=new.csv)

The source code:

/**
  * convert all cr and crlf within quotes to lf
  * convert all other cr or lf to crlf
  */
  data _null_;
    infile &in recfm=n ;
    file &out recfm=n;
    retain isq iscrlf 0 qchar &qchar;
    input inchar $char1. ;
    if inchar=qchar then isq = mod(isq+1,2);
    if isq then do;
      /* inside a quote change cr and crlf to lf */
      if inchar='0D'x then do;
        put '0A'x;
        input inchar $char1.;
        if inchar ne '0A'x then do;
          put inchar $char1.;
          if inchar=qchar then isq = mod(isq+1,2);
        end;
      end;
      else put inchar $char1.;
    end;
    else do;
      /* outside a quote, change cr and lf to crlf */
      if inchar='0D'x then do;
        crblank:
        put '0D0A'x;
        input inchar $char1.;
        if inchar='0D'x then do;
          /* multiple CR indicates CR formatted file with blank lines */
          goto crblank;
        end;
        else if inchar ne '0A'x then do;
          put inchar $char1.;
          if inchar=qchar then isq = mod(isq+1,2);
        end;
      end;
      else if inchar='0A'x then put '0D0A'x;
      else put inchar $char1.;
    end;
  run;

[AllanBowe]

The updated link (I can't edit my old post): https://core.sasjs.io/mp__cleancsv_8sas.html

[Tom]

Your logic does not handle files with TERMSTR=CR that have empty lines.

filename crlf temp;
data _null_;
  file cr termstr=cr ;
  put 'line 1'///'line 4'/'line 5';
run;

%mp_cleancsv(in=cr,out=crlf);

data _null_;
  infile crlf lrecl=100 recfm=f;
  input;
  list;
run;

[AllanBowe]

Thanks @Tom - much appreciated! This is exactly why it is so good to share code. Others can help find the holes in it 🙂

I've fixed the bug, also written a test case to cover it, and refreshed the documentation. The PR is here: https://github.com/sasjs/core/pull/290