FCMP function giving unexpected results in PROC SQL

Question

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").

Does it generate the same value for every SeLECT statement? Or just the same value for every observation in the same SELECT statement? — Tom, Jan 30 '18 at 02:16
Same value for every observation, which is equal to one with every run. I can replicate this using SAS UE/SAS 9.4 TS1M5. The documentation does also say that FCMP functions are valid in PROC SQL, expect functions that use arrays. — Reeza, Jan 30 '18 at 03:04

score 0 · Answer 1 · answered Feb 08 '18 at 08:17

0

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.

answered Feb 08 '18 at 08:17

englealuze

1,445
12
19

1

That would generate a constant value for every observation since the macro function call will evaluate before the SQL code runs. But perhaps it is a step up from always generating `1`. – Tom Feb 08 '18 at 15:44
There are two `sum()` functions in `proc sql`. One of them is the aggregate type SQL function, the other is the equivalent to the data step `sum()` function. The latter can be called by passing in multiple values to the function to force it to resolve to the data step function. – Robert Penridge Feb 08 '18 at 17:15

FCMP function giving unexpected results in PROC SQL

1 Answers1