SAS Program to remove Carriage Returns from .XLS extension files?

[mcook]

Is there a way to programmatically remove carriage returns from .xls files in SAS?  

The Code here (https://support.sas.com/kb/26/065.html) successfully removes carriage returns from CSV files.  Is there a similar code for xls files with multiple sheets?    

[Tom]

---

@mcook wrote:

Is there a way to programmatically remove carriage returns from .xls files in SAS?  

The Code here (https://support.sas.com/kb/26/065.html) successfully removes carriage returns from CSV files.  Is there a similar code for xls files with multiple sheets?    

---

You could search on an EXCEL forum for code to modify XLS files.

But why?  Just import the XLS file(s) into SAS datasets and then you can change the CR or LF characters in the character variables into spaces or some other character.

proc import datafile='myfile.xls' out=myfile replace dbms=xls;
run;
data myfile;
  set myfile;
  array _character_ _character_;
  do over _character_;
    _character_=translate(_character_,'  ','0A0D'x);
  end;
run;

Do you really have old XLS format files?  Not newer XLSX files?

[AllanBowe]

Once it's in CSV you can also use this macro to clean up the carriage returns:  https://core.sasjs.io/mp__cleancsv_8sas.html

Another option is a tool my team have produced, which is free for up to 5 users, and lets you import any version of excel (with data on any sheet, in any row, with columns in any order).  It can also be used to fix the carriage return issue:  https://datacontroller.io