BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Patrick
Opal | Level 21

@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
Barite | Level 11

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;

 

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
AllanBowe
Barite | Level 11
The updated link (I can't edit my old post): https://core.sasjs.io/mp__cleancsv_8sas.html
/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
Tom
Super User Tom
Super User

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
Barite | Level 11
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
/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 42673 views
  • 15 likes
  • 9 in conversation