topic Re: Possible to remove carriage returns from a string and leave the rest of the value? in SAS Programming

Possible to remove carriage returns from a string and leave the rest of the value?

Walternate — Thu, 01 Jun 2023 20:28:53 GMT

Hi,

I have some files which I read in from Excel.

There are some long strings which have carriage returns in the middle. I need to remove the CR but leave the rest of the value intact. Is this possible?

Thanks!

Re: Possible to remove carriage returns from a string and leave the rest of the value?

Kurt_Bremser — Thu, 01 Jun 2023 20:46:28 GMT

Use the COMPRESS function to remove "0d"x.

Re: Possible to remove carriage returns from a string and leave the rest of the value?

Walternate — Fri, 02 Jun 2023 13:32:00 GMT

I tried the following:

directory3 = compress(directory2, '0d'x);

I did not get an error message but the carriage returns are still there. Did I miss some punctuation or a modifier or something?

Re: Possible to remove carriage returns from a string and leave the rest of the value?

ErikLund_Jensen — Fri, 02 Jun 2023 13:44:03 GMT

hi @Walternate

You could try removing '0A'x (the line feed char) also. Might solve your problem.

Re: Possible to remove carriage returns from a string and leave the rest of the value?

Reeza — Fri, 02 Jun 2023 15:22:27 GMT

Use a HEX format on your character string to see the character in the string.

proc print data=have;
var stringVar;
format stringVar $hex50.; *make sure there's an appropriate length;
run;

Re: Possible to remove carriage returns from a string and leave the rest of the value?

ballardw — Fri, 02 Jun 2023 16:40:35 GMT

I strongly suspect that you want to replace the character with a space. Removing the line feed means the resulting text will be poor for reading.

Example:

This is an Excel value
split to two or more
lines.

When you remove the line feeds you will get

This is an Excel valuesplit to two or morelines.

The TRANSLATE function will replace a character with another.

Re: Possible to remove carriage returns from a string and leave the rest of the value?

Walternate — Tue, 06 Jun 2023 15:30:20 GMT

Is there any way to change my output program which built the Excel files so that it is only outputting text values and no weird characters?

To output I am just using ods excel and proc printing a data file.

Re: Possible to remove carriage returns from a string and leave the rest of the value?

Tom — Tue, 06 Jun 2023 15:44:51 GMT

Use the FLOW="TABLES" option.

https://communities.sas.com/t5/ODS-and-Base-Reporting/Lines-Wrapping-in-ODS-Excel/m-p/357530/highlight/true#M18573