Proc SQL bug: truncates user-defined function results to 200 chars

Reply
Frequent Contributor
Posts: 85

Proc SQL bug: truncates user-defined function results to 200 chars

Hi All

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.

options CMPLIB=work.functions;

proc fcmp outlib=work.functions.general;

function utranslate(str $, to $, from $) $ 32767;

  length result $32767;

  result = translate(str, to, from) ;

  return(result);

endsub;

run;


/* Create a long string */

data test;

length str $100  longstr $300;

str='aaaaaaaaaabbbbbbbbbbccccccccccz';

longstr = str || str || str;

run;

data datastep ;

  set test;

  length udf_translate $300;

  /* This works fine */

  udf_translate =  utranslate(longstr, 'x', 'b');

run;

proc sql;

create table sql as

select

   longstr,

   utranslate(longstr, 'x', 'b') as udf_translate length=300 ,

   translate(longstr, 'x', 'b') as sas_translate length=300

from test;

quit;





SQL_truncated_UDF.jpg
Respected Advisor
Posts: 3,156

Re: Proc SQL bug: truncates user-defined function results to 200 chars

Posted in reply to JerryLeBreton

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.

Haikuo

Super User
Posts: 10,044

Re: Proc SQL bug: truncates user-defined function results to 200 chars

colon operator in SQL is  EQT   like =: in data step .

Respected Advisor
Posts: 3,156

Re: Proc SQL bug: truncates user-defined function results to 200 chars

Thanks, Ksharp! Didn't know that.

Haikuo

Ask a Question
Discussion stats
  • 3 replies
  • 279 views
  • 0 likes
  • 3 in conversation