BookmarkSubscribeRSS Feed
mcook
Quartz | Level 8

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?    

 

 

2 REPLIES 2
Tom
Super User Tom
Super User

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

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

/Allan
MacroCore library for app developers
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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 878 views
  • 0 likes
  • 3 in conversation