topic Re: Evaluate Expression Stored in a Dataset in SAS Programming

Evaluate Expression Stored in a Dataset

SelvaN — Sun, 11 Aug 2013 19:25:57 GMT

I have a dataset with variable such as INPUT1, INPUT2, INPUT3, INPUT4, CALCULATION

INPUT1 INPUT2 INPUT3 INPUT4 CALC

4 6 7 9 INPUT1 * INPUT2 + 100

8 8 6 2 INPUT3 / INPUT2

8 3 90 11 INPUT1 - INPUT3

I would like to evaluate the expression stored in CALC and store the result in a new column

Thanks

Selva.

Re: Evaluate Expression Stored in a Dataset

jakarman — Sun, 11 Aug 2013 20:36:45 GMT

can you ellaborate why you are asking this?

Normally SAS is using a table approach (relational like) wiht that it is strange to differentiate cacualtions within a column.

It is has not the fully freedom of a spreadsheet like Excel. It is requiring the normal programmer approach.

There are solutions possible. They are in the more advanced concepts and the googling in the language. As the way you are asking it the SAS language interepretation must be enhanced by a dynamic intrepretation line.

Re: Evaluate Expression Stored in a Dataset

Fugue — Sun, 11 Aug 2013 20:38:44 GMT

The %eval and %sysevalf functions might do the trick for you.

Re: Evaluate Expression Stored in a Dataset

jakarman — Mon, 12 Aug 2013 04:44:54 GMT

@fugue
That is correct under the condition you are working at the macro language and do not use the datastep.

By that you could do a lot but io is cumbersome. The calculations must be limited to these macro functions.

All datastpe IO to be replaces with sysfunc and open/fopen tedious first IO calls to start all programming,

Or use in a datastep call symput with a resolve call. Timing between datastep language / macro language

Other options are:

- converting the input to SAS source just defining variables and do a %include

- using fcmp with run_sasfile having the needed expression stored somewhere

Re: Evaluate Expression Stored in a Dataset

Fugue — Mon, 12 Aug 2013 06:08:48 GMT

Here's what I was thinking Jaap . . . since %eval and %sysevalf can take text as an expression, then you can call %eval and %sysevalf directly from a data step statement.

Re: Evaluate Expression Stored in a Dataset

jakarman — Mon, 12 Aug 2013 06:54:53 GMT

Yes but at that moment datastep variables you need in computation are not available in the macrovar environment.

When you need those datastep variables you have to export them first to the macro-var environment en refer them as macrovars.

The call Execute won't help you. http://www.sascommunity.org/wiki/Call_Execute_in_the_Data_Step

The SAS-macro enviroment and the sas-datastep (statements) are interacting in a special way.

The question of SelvaN is something that could be a challenge in some course to become aware off all this.
In that case we have done the homework.

Re: Evaluate Expression Stored in a Dataset

LinusH — Mon, 12 Aug 2013 10:14:08 GMT

How many different types of calculations do you have? Can they be more complex than in your example?

If there are only a few, you could use if-then logic interpret the calculations and then more or less hard coded execute them.

If there are "many", you could have a pre code that pick up the distinct calculations, and then have a macro generate the if-then code for you.

Additions and subtractions could be simplified by just putting them in a separate variable with the sign.

Not a neat solution, but could work.

Re: Evaluate Expression Stored in a Dataset

andreas_lds — Mon, 12 Aug 2013 10:32:50 GMT

The problem can be solved with a dynamically created data-step, but bear in mind that the performance with a high number of observations will be poor. Data-steps can be created with the following techniques:

The macro language with %sysfunc and functions like open, fetch and close.
data-_null_-step with file and put and afterwards %include to load and execute the generated program
data-_null_-step with call execute - very much like the second technique, but debugging the program will be more difficult.

The following code is not fully tested:

data _null_;

set work.have end= last;

file "SOMEWHERE\calc.sas";

length line $ 200;

if _n_ = 1 then do;

put 'data work.want;';

put 'set work.have;';

put 'length result 8;';

put 'select (_n_);';

end;

line = catx(' ', 'when (', _n_, ') result =', calc, ';');

put line;

if last then do;

put 'end;';

put 'run;';

end;

run;

Re: Evaluate Expression Stored in a Dataset

data_null__ — Mon, 12 Aug 2013 11:57:22 GMT

DOSUBL looks about right.

http://www.lexjansen.com/pharmasug/2013/AD/PharmaSUG-2013-AD24-SAS.pdf

Re: Evaluate Expression Stored in a Dataset

SelvaN — Mon, 12 Aug 2013 13:47:23 GMT

The source has the "calculation" and the "result" stored. But, the values used to get the results is not persisted. We had to bring all the values (INPUT1, INPUT2...) from a different source. So, as part of this conversion, we need to re-calculate the result based on the new values and show the differences if any.

Hope this makes sense.

Selva.

Re: Evaluate Expression Stored in a Dataset

SelvaN — Mon, 12 Aug 2013 13:49:18 GMT

Thanks Andreas,

This is what I ended up doing. In addition to the evaluation of the calculation, I also needed to store the intermediate values. For example, if the calculation is INPUT1 * INPUT2, then I need to store "4.5 * 3" in one column and "10.5" in the result column. So, this method is helping to do that too.

Also, as I have only around 100 combination of these calculation from a dataset with millions of records, I had first selected distinct calculation and had the DATA step built with only the 100 combinations.

Thanks

Selva.

Re: Evaluate Expression Stored in a Dataset

SelvaN — Mon, 12 Aug 2013 13:50:02 GMT

Interesting. I will look into it.

Re: Evaluate Expression Stored in a Dataset

jakarman — Mon, 12 Aug 2013 14:45:36 GMT

SelvaN,
With you addtional information:

- having millions of records

- ca 100 type of calcuations

- Recalculating something.. having many type of sources

I assume the recalculation is triggered /dependent of that type of source.

If you would have an indicator of the calcauation or treat the Calc field as that information.

You could use LinusH approach a fixed part of code... You could generate out of your table.

Later you can do a %include ..... ; of this code of processing.

The idea like:

Select ;

When ( calc=".." ) ...-compute- calc ; /* repeated */

Otherwise put "a message" ;

end;

Re: Evaluate Expression Stored in a Dataset

Fugue — Mon, 12 Aug 2013 23:51:02 GMT

Here's an example that uses sysevalf in a data step.

/* create sample data */

data have ;
format calc_exprsn $50.;
input input1 input2 input3 input4 calc_exprsn $ &;
datalines;
4 6 7 9 INPUT1 * INPUT2 + 100
8 8 6 2 INPUT3 / INPUT2
8 3 90 11 INPUT1 - INPUT3
;
;;;;

/* evaluate expression using sysevalf in a datastep */

data want;
length name $ 8;
set have ;
drop i name;
macro_exprsn = tranwrd ( calc_exprsn, 'INPUT' , '&INPUT' );
array nums ( * ) input1-input4;
do i = 1 to dim (nums);
call vname (nums(i), name);
call symput (name, put(nums(i), best12.));
end;
calc_rslt = resolve ('%sysevalf('||macro_exprsn||')');
run;

Re: Evaluate Expression Stored in a Dataset

SelvaN — Tue, 13 Aug 2013 00:27:59 GMT

Fugue,

Fantastic...This worked like a charm

In fact, in order to store my intermediate values such 4*6+100, all I needed to do was to add another variable without %sysevalf as below.

data want;

length name $ 8;

set have ;

drop i name;

macro_exprsn = tranwrd ( calc_exprsn, 'INPUT' , '&INPUT' );

array nums ( * ) input1-input4;

do i = 1 to dim (nums);

call vname (nums(i), name);

call symput (name, put(nums(i), best12.));

end;

calc_rslt = resolve('%sysevalf('||macro_exprsn||')');

calc_rslt2 = compbl(resolve(macro_exprsn));

run;