PROC SQL truncates result of prxchange function

ErikLund_Jensen — Thu, 20 Sep 2018 14:13:56 GMT

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.

Re: PROC SQL truncates result of prxchange function

gamotte — Thu, 20 Sep 2018 14:19:10 GMT

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;

Re: PROC SQL truncates result of prxchange function

ErikLund_Jensen — Thu, 20 Sep 2018 14:23:07 GMT

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

topic PROC SQL truncates result of prxchange function in SAS Programming

PROC SQL truncates result of prxchange function

Re: PROC SQL truncates result of prxchange function

Re: PROC SQL truncates result of prxchange function