sql does not respect my fcmp function length

Question

Can anybody explain to me how to get PROC SQL to give the results of my custom function the length I specify in the function definition? Datastep does it fine, but SQL gives me the default length of 200 characters.

Here is code that demonstrates the issue:

proc fcmp outlib = work.funcs.funcs ;
  * Note type/length specification ;
  function testy(istr $) $11 ;
    return ('bibbitybobb') ;
  endsub ;
quit ;

options cmplib = work.funcs ;

data from_dstep ;
  set sashelp.class ;
  tes = testy(name) ;
run ;

proc sql ;
  create table from_sql as
  select *
        , testy(name) as tes
  from sashelp.class
  ;

  describe table from_dstep ;
  describe table from_sql ;

quit ;

My log on that is:

47           describe table from_dstep ;
NOTE: SQL table WORK.FROM_DSTEP was created like:

create table WORK.FROM_DSTEP( bufsize=65536 )
  (
   Name char(8),
   Sex char(1),
   Age num,
   Height num,
   Weight num,
   tes char(11)
  );

48           describe table from_sql ;
NOTE: SQL table WORK.FROM_SQL was created like:

create table WORK.FROM_SQL( bufsize=65536 )
  (
   Name char(8),
   Sex char(1),
   Age num,
   Height num,
   Weight num,
   tes char(200)
  );

As you can see, the datastep gave me a length of 11 on my 'tes' variable, but sql gives me 200.

Is there a way to get length 11 when using SQL?

score 2 · Accepted Answer · answered Aug 24 '17 at 23:57

Unfortunately, I don't think so. SQL and data step work differently in this regard, other built-in functions have some of the same issues (CATS/CATX for example have different defaults in SQL than in data step). I think it has to do with how compilation works in the data step vs. interpretation in SQL. I believe I've seen something specifying this was expected behavior, but I can't seem to find it right now; if you'd like more detail and nobody else here can provide it, perhaps start a track with SAS support and see what they say.

You can directly set it in SQL of course:

proc sql ;
  create table from_sql as
  select *
        , testy(name) as tes length 11
  from sashelp.class
  ;
quit;

sql does not respect my fcmp function length

1 Answers1