BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Amir
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5
Yes, tranwrd() worked, thank you so much! I wasn't sure it would inside proc sql.
Amir
PROC Star

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
Super User Tom
Super User

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
Super User Tom
Super User

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
Super User Tom
Super User

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
Calcite | Level 5

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. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 23 replies
  • 19294 views
  • 1 like
  • 6 in conversation