Proc FCMP Array

[afsand]

Hi Everyone,

I have created a proc fcmp function called getmortq that will read the mortality rate from a data step table (SASDATA.QX_WB_F). I am using the the read_array function to read the mortality table from the data step table and save it in an array.

My issue is that the code is too slow because I have 700k observations in the ADHERANT table, at each iteration the proc fcmp function will read the SASDATA.QX_WB_F table but I need to read it only for the first observation and use the readed array for the other observations. But it seems like SAS does not save in memory the arrays, at each iteration the QX_WB_F array is deleted and I cannot have access to the array anymore I need to read the data step table again.

How can I save the array in memory for the first observation and use it for the other observations?  

proc fcmp outlib=work.funcs.sql;

function getmortq(mort_type $, sexe, indProduit, age, dt_nais, Eval_dt2, AnEval2, obs);

if MORT_TYPE = 'LB' then age_lookup = age;

if MORT_TYPE = 'NB' then do;
if month(dt_nais) >= month(Eval_dt2) then do;
age_lookup = age + round(1-( month(dt_nais)/12 - month(Eval_dt2)/12),1);
end ;
else do ;
age_lookup = age + round(( month(Eval_dt2)/12 - month(dt_nais)/12),1);
end ;
end;

if obs=1 then do ;
array QX_WB_F[1] / nosymbols;
rc = read_array('sasdata.QX_WB_F', QX_WB_F);
end;
qx = QX_WB_F[min(121,age_lookup+1),2+year(Eval_dt2)-AnEval2];

return(qx);

endsub;

options cmplib = work.funcs;

data _null_;

set adherant;

MORT_TYPE = "&MORT_TYPE.";

if _n_=1 then obs=1;
qx = getmortq(mort_type, sexe, indProduit, age_adh, dt_nais_adh,&date_eval., &AnEval., obs);

run;

[ballardw]

What is the exact relationship between your data set Adherant and result of the function. Without examples of both then its pretty hard to even guess what your function is doing.

Since by definition a function is called every time it is requested in a data step there really isn't a way to avoid that AS A FUNCTION call.

If the SASDATA.QX_WB_F is basically parameters then likely what you want is to populate the parameter values so the data step can use them and the move most of the calculations you have in that function into a data step using those parameters. That might mean simpler function that basically just does the read_array to populate the variables and combining that set with the Adherant.

Or provide examples of both data sets and the expected results.

[afsand]

Hi Ballardw, 

thanks for your reply. I attached the two tables, I do not want to merge the two tables because I calculate some variables in a data step that I use to extract the information from the QX_WB_AF table that is why I use a function that will read the mortality rate at each iteration of the Adherant table. 

My only concern is that I am currently using read_array at each iteration, but I need to read it only for the first iteration. The read array function is only available within a proc fcmp procedure, that is why I created a function for that. Do you know if Data step has a function that can do the same thing: read a data table and import it in an array? 

[yabwon · Accepted solution]

Check out this link:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1hibagzq56n1ln18uuirjot40in.htm

maybe the STATIC statement will help here.

Bart

[afsand]

OMG you saved my life. it works very well!!!!!!!!!!!!!!!!

Thank you so much for your help!