CODEWITHSUNDEEP
randomsasproc-sqlfcmp
Robert Penridge
Robert Penridge

Reputation: 8513

FCMP function giving unexpected results in PROC SQL

I wanted to take Rick Wicklin's macro (https://blogs.sas.com/content/iml/2015/10/05/random-integers-sas.html) that generates random numbers and convert it into an FCMP function. The FCMP function works as expected when called using %sysfunc(randbetween(1,100)); or via a data step, but from proc sql it always returns a value of 1. 

proc fcmp outlib=work.funcs.funcs;
  function randbetween(min,max);
    return ( min + floor( ( 1 + max - min ) * rand("uniform") ) );
  endsub;
run;

Working example:

data example;
  do cnt=1 to 1000;
    x = randbetween(1,100);
    output;
  end;
run;

Broken SQL example:

proc sql noprint;
  create table have as
  select randbetween(1,100)
  from sashelp.class
  ;
quit;

Is this a bug? Or am I mis-using something?

SAS version is SAS9.4TS1M5. Windows 10x64.

EDIT: When I subsitute the formula directly into the SQL statement it works as expected, so it seems unrelated to the call to rand("uniform").

Upvotes: 1

Views: 254

Answers (1)

englealuze
englealuze

Reputation: 1663

I think this is because procedure and data steps generated different environments in SAS. Normally functions in data steps and functions in proc sql are different attributes, even they have same names. An example is sum() function.

In your case, you can also use %sysfunc(randbetween(1,100)) within the proc sql closure. 

proc sql noprint;
  create table have as
  select %sysfunc(randbetween(1,100))
  from sashelp.class
  ;
quit;

This should give you the expected result.

Upvotes: 0

Related Questions