How do I preserve line breaks in string variable?

[Amir]

Are you looking for the byte() function in SAS?

Perhaps show us the proc sql code so that we can better understand the context.

Regards,

Amir.

[mmmaria]

The proc sql looks something like this at the moment:

proc sql;

select 
translate (template_txt, '0D0A'x, '~') as template_txt
into :mvPatternTxt separated by ' '
from <Oracle table> dict
where dict.template_id = 123;

quit;

The final code will be more complicated (case clause on template_txt field depending on which dictionary table is being used (table name postfix determined by variable, replace line breaks if it's "...ABC", else select template_txt as is) + multiple other fields selected as is) but I'm trying to get at least this to work for now. Currently it's replacing line breaks with blanks/spaces just like at the very beginning.

I thought maybe the issue was with encoding or something of the sort but it won't even replace/pick up numbers like '1' or ordinary letters. 

[Amir]

If I've understood correctly, you're trying to do what I initially posted, i.e., use tranwrd() in the proc 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;

This shows the following in the log:

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

Regards,

Amir.

[mmmaria]

Yes, tranwrd() worked, thank you so much! I wasn't sure it would inside proc sql.

[Amir]

Glad to hear you got it working. Remember to mark whichever post you feel appropriate as the solution.

If you have any further queries then please create a new question and you can always provide a link back to this one if necessary.

Thanks & kind regards,

Amir.

[Tom]

That code does not look right.  You have two characters you want to translate into, but have listed only one character you want to translate from.  Also you seem to have the arguments backward as you are trying to convert tildas into carriage returns instead of the other way around.

[Tom]

Be careful using TRANSLATE().  Not sure how the Oracle function works, but the SAS function has the arguments in the opposite order than you would normally think.  The new values come first and the old values come second.  And it translates individual characters.

So to translate linefeed to tilde you would code:

translate(varname,'~','0A'x);

If you do want to use Oracle functions then make sure to use explicit pass thru code.

[Tom]

In general there should not be any trouble reading/writing CR or LF characters from Oracle.

Try a simple test to confirm.  Read a value from one oracle table into a SAS dataset.  Then write it back into Oracle.

Once you see that it works then go through your current code and figure out where you a "losing" the line breaks.

You mention putting the values into macro variables. Why are you doing that?  See what happens if you change your code to just keep the values in dataset variables instead.

For example instead your code could just be something like this instead to concatenate the values of TEMPLATE_TXT into MSG_TXT.

data mvTable;
  set <oracle table>  end=eof;
  length msg_txt $32000;
  msg_txt=catx(' ',msg_txt,template_txt);
  retain msg_txt;
  if eof;
run;

[mmmaria]

I have previously explained why I need the values to be in macro variables. They're passing through a VAST amount of different SAS scripts and macros on their way from the source table to the destination table. 

The translate() function I was using was an Oracle function, not a SAS function. Its arguments are in perfectly correct order. 

tranwrd() is working but now I'm having trouble with it truncating my data to 200 characters. May or may not make a separate post for that.