PROC SQL truncates result of prxchange function

[ErikLund_Jensen]

Hi

I have a truncation problem with the prxchange function in proc sql, as illustrated in the attached code example. The output string can be up to 609 chars long, and it has the defined length, but the content is truncated to 200 chars.

This truncation does not occur when the function is used in a data step, but I want to use the function in a DI Studio, and company policy requires us to use standard transformations (in this case Extract) and avoid user-written code as much as possible.

So I hope somebody has an idea to fix or circumvent this problem (other than a user-written data step).

* Real data example - string can have max. length of 609 chars;
data a;
	length origstring $609;
	origstring = 'Odense Kommune\Ældre- og Handicapforvaltningen\Drift\Ældre og Handicap Nord\Forløb medfødt hjerneskade Nord\Botilbud medfødt hjerne' ||
	'skade Nord 2\Bo- og Støttetilbudet Nord-Vest\Bo- og Støttetilbud Hvedevangen';
run;

* Prxchange used in data step - works as expected;
data b; set a;
	length xlatestring $609;
	xlatestring = prxchange('s/\s?[&,\/\+]\s?/_/',-1,prxchange('s/([AI])\/(S)/$1$2/',-1,origstring ));
run;

data _null_; set b;
put origstring;
put xlatestring;
run;

* Prxchange used in proc sql - truncates to 200;
proc sql;
create table c as
	select 
		origstring,
		prxchange('s/\s?[&,\/\+]\s?/_/',-1,prxchange('s/([AI])\/(S)/$1$2/',-1,origstring )) as xlatestring length=609
	from a;
quit;

data _null_; set c;
put origstring;
put xlatestring;
run;

Tested in Windows 9.4m3 64 bit and Linux 9.4m5 64 bit.

[gamotte · Accepted solution]

Hello,

You have a prxchanged string in your prxchange which is not given a length, hence the truncation.

proc sql;
create table c(drop=tempstr) as
	select 
		origstring,
		prxchange('s/([AI])\/(S)/$1$2/',-1,origstring ) as tempstr length=609,
		prxchange('s/\s?[&,\/\+]\s?/_/',-1,calculated tempstr) as xlatestring length=609
	from a;
quit;

[ErikLund_Jensen]

Of course. Thank you very much! - stupid of me not to figure that out myself.