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.
Please post your sample.csv as attachment so we can deal with the real thing and stop with all the guesswork.
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.