TRANWRD function in SAS DI Studio expression

[dsadsad]

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

[ballardw]

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

[Patrick]

@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#p10atr6djv8t88n16x... 

[dsadsad]

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

[Patrick]

@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;

[dsadsad]

Thanks Patrick

[ErikLund_Jensen]

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;