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

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

mcook — Fri, 10 Dec 2021 16:41:56 GMT

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?

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

Tom — Fri, 10 Dec 2021 18:47:52 GMT

@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?

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

AllanBowe — Sun, 12 Dec 2021 16:30:28 GMT

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