Hi all,
Right now I think this could be a bug. However, it could be me not understanding type conversion in the FedSQL system or limitation of the RAND function. Please consider this code. I have condensed this down from something more complicated, so there is no obvious use case for this example:
proc ds2;
package mytools / overwrite =yes;
method mymethod(Double Value,Double rand) returns double;
declare double returnVal;
returnVal = Value;
if (rand > .5) then returnVal = 0;
return (returnVal);
end;
run;
run;
quit;
data have(drop=y);
do a = 1 to 50;
r = rand('UNIFORM');
x = (a / 5000);
output;
end;
x = 0;
do y = 1 to 5;
r = rand('UNIFORM');
output;
end;
run;
proc fedsql;
drop table want_rand force;
drop table want_fixed force;
create table want_rand as
select * from
(select x,r,mytools.mymethod(x,rand('UNIFORM')) as t
from have) as w
where t > 0;
create table want_fixed as
select * from
(select x,r,mytools.mymethod(x,r) as t
from have) as w
where t > 0;
quit;
data want_rand;
set want_rand;
format t 10.7;
run;
What I can't explain is the rows where t = 0 in the want_rand table. The query is supposed to only select values where it is > 0. The want_fixed table does not have these t = 0 rows even though the only difference between these queries is passing the value of the variable "r" which is predefined as opposed to passing a randomly generated number directly. The method should be setting about 50% of the values to 0. Both of the queries returns about the same amount of rows, even though the want_rand table has several 0 values for t. I interpret this to mean that the query is subsetting correctly, but the conversion from FedSql output to a SAS dataset is rounding them to 0 after the fact. However there seems to be no correlation to the size of x and those with t=0, so rounding due to type conversion doesn't explain it very well. Notice that the method does not use the value of "rand" to do any calculation, it is only used as a condition. So how would the different values passed change the datatype or formatting of the output variable at all? The final data set id just to assign a more detailed numeric format to "t" and rule out this being a display format issue.
I tried moving the call to RAND inside the DS2 method and got the same result as when passing it as a parameter. So could this have something to do with calling RAND in the FedSQL environment? The same exact logic inside a FCMP function called in PROC SQL works as expected.
So, like I said, I'm about to submit this to tech support. I am hoping one of you will educate me as to why this is expected behavior.
Thanks!
... View more
