topic Re: TRANWRD function in SAS DI Studio expression in SAS Data Management

TRANWRD function in SAS DI Studio expression

dsadsad — Tue, 23 Apr 2019 00:27:47 GMT

Hi DI Experts ,

Field length is restricted to 200 char when I am using tranwrd function to replace Tab and carriage return in the expression column in the field mappings in the table loader transformation. i have mentioned 1500 as length but still it is only limiting to 200

Source is Teradata and Target is Oracle . Below is the expression used.

tranwrd(tranwrd(OFFER_REASON, '09'x,''),'0d0a'x,'')

Any work around to get away with it ?

Thanks in Advance

Re: TRANWRD function in SAS DI Studio expression

ballardw — Tue, 23 Apr 2019 17:02:10 GMT

How did you "mention 1500 as length"?

You should provide at least the code for the entire proc or data step using the code.

If you are attempting to replace with no character, i.e a zero length string, you may want the TRANSTRN function instead of TRANWRD. TRANWRD will replace with a single space

Re: TRANWRD function in SAS DI Studio expression

Patrick — Tue, 23 Apr 2019 23:15:07 GMT

@dsadsad

Looks like you just want to replace some control characters with blanks. You could use KTRANSLATE() for this.

ktranslate(offer_reason,'a0a0a0'x,'090d0a'x)

http://support.sas.com/documentation/cdl//en/nlsref/69741/HTML/default/viewer.htm#p10atr6djv8t88n16x5xgh2l30t5.htm

Re: TRANWRD function in SAS DI Studio expression

dsadsad — Tue, 23 Apr 2019 23:49:02 GMT

Hi Patrick , Thanks for responding . looks like ktranslate option is only runs on Z/OS as per the documentation. we use Linux Redhat.

Re: TRANWRD function in SAS DI Studio expression

Patrick — Thu, 25 Apr 2019 00:41:40 GMT

@dsadsad

I've actually tested the code I've posted with SAS under Linux and it works. I'm not sure what this z/OS specific comment in the documentation tries to express but it can't be that the function only works for z/OS.

data test;
  offer_reason=cats('abcd','09'x,'xyz','0d0a'x,'abcd');
  offer_reason=ktranslate(offer_reason,'a0a0a0'x,'090d0a'x);
run;

....and with Linux you don't have to repeat the To value in the function if you just want it to become a blank. Below simplified version does the job as well.

data test;
  offer_reason=cats('abcd','09'x,'xyz','0d0a'x,'abcd');
  offer_reason=ktranslate(offer_reason,' ','090d0a'x);
run;

Re: TRANWRD function in SAS DI Studio expression

dsadsad — Fri, 26 Apr 2019 04:25:18 GMT

Thanks Patrick

Re: TRANWRD function in SAS DI Studio expression

ErikLund_Jensen — Fri, 26 Apr 2019 13:02:10 GMT

Hi @dsadsad

It seems that you have got a solution, but I would like to explain your problem.

You use 2 functions in the same expression, which means that SAS creates a temporary variable to hold the result of the inner TRANWRD, which is then used as input to the outer TRANWRD. This variable is created with a default length of 200, and there is nothing you can do to change it.

But a workaround is to split the expression in 2 using a temporary column like in this example:

data have;
	length a $10 str $500;
	do x = 1 to 50;
		a = strip(put(x,2.))||'.................';
		str = catt(str,a);
	end;
run;

proc sql;
	create table bad as
		select tranwrd(tranwrd(str,'.','-'),'-','+') as str length=500
		from have;
quit;

proc sql;
	create table good (drop=tmp) as
		select
			tranwrd(str,'.','-') as tmp length=500,
			tranwrd(calculated tmp,'-','+') as str length=500
		from have;
quit;