Applying multiple formulas located in a column to calculate rates

[linaibrou]

I have a CSV file that has a character variable of multiple formulas which reference the other variables in the dataset. I have tried to use the call execute function to apply that and it worked for only like 4 variables but I have 50 variables and 20 different formulas and feel like there is an easier way to resolve these formulas. I have 3500 obs in the dataset so using the call execute is taking forever.

My code below is just super simple using call execute. Could I use an array? Any help would be awesome!

data temp1;
format formula $200.;
input formula $ num denom num_admin num_ehr elig_pop;
datalines;
num/denom 17 250 . . .
num_admin+num_ehr/elig_pop . . 5 10 350
num+num_admin+num_ehr/elig_pop 14 . 4 18 400
;
run;

data temp2;
set temp1;
if _n_ eq 1 then call execute('data result;');
call execute(cats('num=',num,';'));
call execute(cats('denom=',denom,';'));
call execute(cats('num_admin=',num_admin,';'));
call execute(cats('num_ehr=',num_ehr,';'));
call execute(cats('elig_pop=',elig_pop,';'));
call execute(cats('rate=',formula,';'));
call execute('output;');
if last then call execute('run;');
run;

[Reeza · Accepted solution]

Not sure how the 3500 obs come into play but based on what you posted this should work. A bunch of lines in there seemed unnecessary.

data temp1;
format formula $200.;
input formula $ num denom num_admin num_ehr elig_pop;
datalines;
num/denom 17 250 . . .
num_admin+num_ehr/elig_pop . . 5 10 350
num+num_admin+num_ehr/elig_pop 14 . 4 18 400
;
run;

data temp2;
set temp1 end=last;
if _n_ eq 1 then call execute('data result; set temp1;');
call execute(cat('if _n_ =', _n_, ' then rate=',formula,';'));
if last then call execute('run;');
run;

[linaibrou]

Thank you! I think I was overthinking it. This worked. Appreciate it!

[ballardw]

You likely have an issue with how well that text represents calculations.

For example you show this:

num_admin+num_ehr/elig_pop . . 5 10 350

A direct translation to SAX code is going to give you the result of num_ehr/elig_pop plus num_admin. But from the third line I really wonder if the interpretation isn't supposed to be

(num_admin+num_ehr)/elig_pop . . 5 10 350

Where num_admin is added to num_erh and that total is then divided by elig_pop.

Which is correct?

Arrays really mainly come into power when you do the exact same operation (or at least repeated) for different variables. You haven't shown any example that demonstrates that. You would likely have to show examples of many more calculations. If you formulae are similar enough then the pattern should jump out and let us see if some array might be possible.

Since all of these:

call execute(cats('num=',num,';'));
call execute(cats('denom=',denom,';'));
call execute(cats('num_admin=',num_admin,';'));
call execute(cats('num_ehr=',num_ehr,';'));
call execute(cats('elig_pop=',elig_pop,';'));

create code that looks like

varname=varname; 

They are pretty much not needed unless you just want to write/generate ugly redundant code.

[Ksharp]

data temp1;
format formula $200.;
input formula $ num denom num_admin num_ehr elig_pop;
datalines;
num/denom 17 250 . . .
num_admin+num_ehr/elig_pop . . 5 10 350
num+num_admin+num_ehr/elig_pop 14 . 4 18 400
;
run;
data want;
 set temp1;
 array x{*} num--elig_pop;
 do i=1 to dim(x);
   formula=prxchange(cats('s/\b',vname(x{i}),'\b/',x{i},'/i'),-1,formula);
 end;
 want=input(resolve(cats('%sysevalf(',formula,')')),best32.);
 drop i;
 run;