How do I preserve line breaks in string variable?

[mmmaria]

Hi, 

I'm trying to find a way to preserve line breaks/carriage returns in a string variable. Everywhere I've looked people are getting rid of them and I'm dealing with an opposite issue. 

Here's an example of what I'm dealing with:

%let mvPatternTxt = nullTxt; 

proc sql;
select TEMPLATE_TXT
into :mvPatternTxt separated by ' ' 
from <Oracle table containing template text with line breaks>
where <...>;

quit;

data mvTable;
MSG_TXT = "%nrbquote(&mvPatternTxt.)";
run;

The field from my Oracle table which is recorded into mvPatternTxt in the proc sql looks something like this and this exact formatting must be preserved in the end result:

"Hello,

this is some text containing line breaks


1 2 3"

Currently the end result looks like this:

"Hello, this is some text containing line breaks 1 2 3" 

Is there a way to preserve line breaks inside a string variable and why are they automatically removed during data step/using nrbquote (I've tried bquote and superq getting the same result)? Thanks!

[Kurt_Bremser]

Why the macro detour?

data mvtable;
set
  <Oracle table containing template text with line breaks>
  (where=( <...>))
  end=done
;
length msg_txt $32767; /* maximum */
retain msg_txt;
msg_txt = catx(' ',msg_txt,template_txt);
if done;
keep msg_txt;
run;

[mmmaria]

Hello Kurt,

Thank you for your answer! Loading the entire table into SAS wouldn't be optimal as it is very large. My code snippets are taken from a bigger SAS program featuring various macros, and msg_txt is later used in some of them, so it needs to be a macro variable. 

Perhaps there's a way to modify the table contents placing some sort of special symbol to signify line breaks that would be interpreted by SAS correctly? I've tried putting /n but it didn't get interpreted as a line break.

I'm also looking for a confirmation (?) that SAS data step will remove all line breaks if that is the case, so if anyone can link me to such confirmation that would be helpful. 

[Amir · Accepted solution]

Hi @mmmaria,

Have you considered a replacing the line breaks with a character that isn't used in the same variable and then create a line break when required?

E.g. The following code sets up the data with line breaks and then replaces them when selected in SQL:  

/* create data with line breaks; hex value '0A' is newline in Unix */
data have;
   length template_txt $ 200;

   template_txt = cats('"Hello,','0A'x,'0A'x,'this is some text containing line breaks','0A'x,'0A'x,'0A'x,'1 2 3"');
run;

/* select and replace line breaks with a unique character */
proc sql noprint;
   select
      tranwrd(template_txt,'0A'x,'~')
   into
      :mvPatternTxt separated by ' '
   from
      have
   where
      1 /* your criteria */
   ;
quit;

%put mvPatternTxt = &mvPatternTxt;

The above gives:

mvPatternTxt = "Hello,~~this is some text containing line breaks~~~1 2 3"

Assuming you later need to print out the value of &mvPatternTxt, you can do some text processing (e.g., using the scan() function) to display the different parts on different lines.

Kind regards,

Amir.

[DWilson]

---

@Amir wrote:

Hi @mmmaria,

Have you considered a replacing the line breaks with a character that isn't used in the same variable and then create a line break when required?

E.g. The following code sets up the data with line breaks and then replaces them when selected in SQL:  

/* create data with line breaks; hex value '0A' is newline in Unix */
data have;
   length template_txt $ 200;

   template_txt = cats('"Hello,','0A'x,'0A'x,'this is some text containing line breaks','0A'x,'0A'x,'0A'x,'1 2 3"');
run;

/* select and replace line breaks with a unique character */
proc sql noprint;
   select
      tranwrd(template_txt,'0A'x,'~')
   into
      :mvPatternTxt separated by ' '
   from
      have
   where
      1 /* your criteria */
   ;
quit;

%put mvPatternTxt = &mvPatternTxt;

The above gives:

mvPatternTxt = "Hello,~~this is some text containing line breaks~~~1 2 3"

Assuming you later need to print out the value of &mvPatternTxt, you can do some text processing (e.g., using the scan() function) to display the different parts on different lines.

Kind regards,

Amir.

---

After creating a data set from proc sql, why not just apply tranwrd again to replace the ~ with '0A'x ? No further text processing required?

[Amir]

@DWilson, Thanks for your suggestion, that was my original thinking too, but, before I did the text substitution, I tried copying the result of &mvPatternTxt, as displayed in the log and pasting it into a text editor and no line breaks appeared (I also looked at the hex characters in the text editor). This appears to be different to what @Tom has experienced. So that is why I suggested the approach I did and to manually process the text when required.

Subsequent to the post from @yabwon, I tried writing the value of &mvPatternTxt directly to an external file using a data step and I then do see the line breaks required:

Hello,

this is some text containing line breaks


1 2 3

Perhaps I have a different setup.

Kind regards,

Amir.

[Tom]

---

@Amir wrote:

@DWilson, Thanks for your suggestion, that was my original thinking too, but, before I did the text substitution, I tried copying the result of &mvPatternTxt, as displayed in the log and pasting it into a text editor and no line breaks appeared (I also looked at the hex characters in the text editor). This appears to be different to what @Tom has experienced. So that is why I suggested the approach I did and to manually process the text when required.

Subsequent to the post from @yabwon, I tried writing the value of &mvPatternTxt directly to an external file using a data step and I then do see the line breaks required:

Hello,

this is some text containing line breaks


1 2 3

Perhaps I have a different setup.

Kind regards,

Amir.

---

So it sounds like you are getting the line breaks in your macro variable.

What is it that you need to do that you cannot do now?

[Amir]

@Tom, agreed the line break does exist in the macro variable, but as @DWilson has also experienced, depending on how / where it is displayed the line break is not always honoured.

As to your question:

What is it that you need to do that you cannot do now?

To be clear, I was just trying to come up with a way forward for the OP (@mmmaria), but as mentioned the line break ('0A'x) isn't always present when copying and pasting from the log for me. As in my last post, there are probably differences in setup at play here.

Until it is made clear how the text is to be put to use by the OP, I think it won't be easy to come to a conclusive solution.

Thanks & kind regards,

Amir.

[DWilson]

---

@Amir wrote:

@DWilson, Thanks for your suggestion, that was my original thinking too, but, before I did the text substitution, I tried copying the result of &mvPatternTxt, as displayed in the log and pasting it into a text editor and no line breaks appeared (I also looked at the hex characters in the text editor). This appears to be different to what @Tom has experienced. So that is why I suggested the approach I did and to manually process the text when required.

Subsequent to the post from @yabwon, I tried writing the value of &mvPatternTxt directly to an external file using a data step and I then do see the line breaks required:

Hello,

this is some text containing line breaks


1 2 3

Perhaps I have a different setup.

Kind regards,

Amir.

---

The carriage return/line feed character is non-printable. I suppose that SAS strips those out when "printing" to the log file. When you then copy the string in the log you are copying a string that has no non-printable character (by definition I guess.)

Hrm, Tom says he copies the string from the log and the non-printing characters are there. Maybe he's using HTML log output and not listing? I wouldn't think that non-printing characters would be preserved in a log using list output; but what do I know.....

SIGH: Actually, I only use list output and I'm not sure if the different output settings actually changes how log output is stored.  I suspect I'm off-base about something.....

Ok, I just tested this with SAS EG on windows and SAS 9.4 TS1M3 running on a remote linux SAS grid environment.

x="Test"||"0A"x||"This";

printing this with list output produces:

TestThis

printing this with HTML output produces:

Test This

Printing this with SAS report output produces:

Test

This

In all three cases, putting to the log shows:

TestThis

[Tom]

What makes you think the line breaks are gone?

Let's do a little test.  First create a macro variable with CR and LF characters in it.

data _null_;
  call symputx('mvPatternTxt'
  ,cat('AAA','0D0A'x,'BBB','0D0A'x,'CCC')
  );
run;

Now pull the value into a dataset variable.  You can use SYMGET() or just expand the macro variable inside quotes to make a string literal. Either way the "line break" characters are still there.

data test;
  length str1 str2 $200;
  str1=symget('mvPatternTxt');
  str2="%superq(mvPatternTxt)";
  put (str1 str2) (/=);
  put (str1 str2) (/= $hex26.);
run;

So you can tell from the hex codes that the OD and OA characters are still there.

str1=4141410D0A4242420D0A434343
str2=4141410D0A4242420D0A434343

And on the SAS log window it might look like they are not there.

But if I copy and paste the text from the log they are there.

str1=AAA
BBB
CCC
str2=AAA
BBB
CCC

[yabwon]

One small note. Instead copying from log we can just print them (with \r\n) into the text file.

filename t temp;
data _null_;
  file t;
  length str1 str2 $200;
  str1=symget('mvPatternTxt');
  str2="%superq(mvPatternTxt)";
  put str1 / str2;
run;

All the best

Bart

[mmmaria]

Hi Tom, everyone - 

the end destination of this text containing line breaks is a different Oracle table, which it's written into after passing through a series of other SAS scripts and macros. The destination table field value contains no line breaks and that's how I know they get lost in the process. We need the line breaks bc the text is an instruction and is illegible without them. It's a shame if removing line breaks is a feature of SAS.

Thanks everyone for your suggestions and participation, along with some colleagues we've also come up with the strategy to convert line breaks to special symbols and convert them back before writing to the destination table. Hopefully we'll manage to implement this. 🙂

[yabwon]

Hi,

the initial data contain end of line symbols, right? They are also extracted from oracle database into some sas dataset, right? 

If at the entrance to the process you have data(with 0Ax and 0Dx) in a variable it must be something wrong with data processing.

Working on variables itself doesn't remove 0Ax and 0Dx (check out the code below).

data have;
  var1 = cat('A  A  A','0D0A'x,'B  B  B','0D0A'x,'C  C  C');
run;

data want;
  set have;
  var2 = var1;
  call symputX('mvPatternTxt', var2);
run;

filename t temp;
data _null_;
  set want;
  file t;
  length str1 str2 $200;
  str1=symget('mvPatternTxt');
  str2="%superq(mvPatternTxt)";

  var3 = compbl(var1);

  put str1 / str2 / var1 / var2 / var3;
run;

All the best

Bart

[Kurt_Bremser]

I've played around with this problem. The linefeeds are removed when you call symput or call execute (e.g. a title statement).

[mmmaria]

So I've tried to first replace the line breaks in Oracle using 

translate (column, chr(10), '~')

which worked. I put chr(10)||chr(13) but 10 is what got picked up.

And now I'm trying to convert this into the proc sql in my script and the line breaks (I've tried putting '0D'x, '0D0A'x and '0D'x||'0A'x since it won't recognize chr()?) are not getting picked up, nor does any character I've tried. Any ideas how to use the Oracle translate function inside a proc sql or what to replace it with? Sorry this is getting a little off topic.