Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Applying multiple formulas located in a column to calculate rates

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 05-01-2023 03:30 PM
(211 views)

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;

- Tags:
- call execute
- Formulas

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.