06-28-2014 10:21 PM
I've been caught out by a SAS bug while using user-defined functions (created by Proc FCMP) in Proc SQL - and thought everyone should know about it.
If you create a user-defined function that returns character strings longer than 200, and use them in Proc SQL, they will be truncated to 200 chars, irrespective of the length= attribute you may use.
The interesting (scary) thing is that the same function works fine in a Data Step.
Seems Tech Support are aware of the issue and suggested I use a Data Step instead of SQL but since I'm using the functions in DI Studio transformations and EG Query Builder, the suggestion isn't much use.
This is dangerously inconsistant behaviour and I don't understand why SAS hasn't considered this a problem that requires urgent action!
The code below illustrates the issue by using the Translate function, both on its own and wrapped inside a FCMP defined function (and the attached shows the result) . Just run this with any version/platform (as far as I can tell) and you'll see that the column udf_translate gets truncated to 200 characters by Proc SQL.
proc fcmp outlib=work.functions.general;
function utranslate(str $, to $, from $) $ 32767;
length result $32767;
result = translate(str, to, from) ;
/* Create a long string */
length str $100 longstr $300;
longstr = str || str || str;
data datastep ;
length udf_translate $300;
/* This works fine */
udf_translate = utranslate(longstr, 'x', 'b');
create table sql as
utranslate(longstr, 'x', 'b') as udf_translate length=300 ,
translate(longstr, 'x', 'b') as sas_translate length=300
06-28-2014 10:43 PM
Thanks for sharing, Jerry. It does occur to me many times that some SAS functions do not function the way they do in the data step. The following incidence spring into my mind:
1. The colon operator. =: or in: stop working in Proc SQL
2. the ?? error suppressor , such as input(var, ??best.), stop working in Proc SQL
3. some run time functions, such as inputn(), inputc(), seems not working with Proc SQL.
They are definitely more, I can't recall for now. I won't call them bugs though.