Hi @MortenAgerlin
As @FreelanceReinh suggests, this happens because most string functions i SAS, especially functions like prxchange, tranword etc, which might change the length of a string, assigns a default length of 200 to the returned result from the function, if the returned variable is created by the function. If the returned value is assigned to an existing variable, e.g. created with a Length Statement, the length of the variable is not changed.
A single function behaves the same way in Proc SQL and Data Steps, but there is a difference behind the scene if functions are nested. In a Data Step, the set of nested functions seems to executed as a single unit, so the given length applies to all function results in the nesting, but in Proc SQL there are "invisible" intermediate results which defaults to 200, so only the result from the outermost function is assigned the specified length.
The following code demonstrates the behaviour, and the last example also demonstrates a workaround, so the intermediate results can be explicitly declared and assigned a length and also be dropped from output, so only the final result is kept.
data have;
length Text $512;
Text =
'This is a long text to be used as an example of how Proc SQL can return unexpected results from SAS' ||
' function calls if functions are nested in a SQL clause. This happens because only the result from ' ||
' the outermost function can be assigned an explicit length, while results from embedded functions ' ||
' are assigned a default length of 200 bytes regardless of the length of argument strings.';
run;
data want0;
set have;
length ChangedText $512;
ChangedText = propcase(tranwrd(Text,'SAS','sas'));
run;
proc sql;
create table want1 as
select
Text,
propcase(tranwrd(Text,'SAS','sas')) as ChangedText length=512
from have;
quit;
proc sql;
create table want2 (drop=tmp) as
select
Text,
tranwrd(Text,'SAS','sas') as tmp length=512,
propcase(calculated tmp) as ChangedText length=512
from have;
quit;
... View more
