Evaluate Expression Stored in a Dataset

[SelvaN]

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.

[jakarman]

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. 

[SelvaN]

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.

[Fugue]

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

[jakarman]

@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 

[Fugue]

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.

[jakarman]

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.

[LinusH]

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.

[andreas_lds]

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:

1. The macro language with %sysfunc and functions like open, fetch and close.
2. data-_null_-step with file and put and afterwards %include to load and execute the generated program
3. 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;

[SelvaN]

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.

[data_null__]

DOSUBL looks about right.

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

[SelvaN]

Interesting. I will look into it.

[jakarman]

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;

[Fugue · Accepted solution]

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;

[SelvaN]

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;