DATA Step, Macro, Functions and more

Evaluate Expression Stored in a Dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Evaluate Expression Stored in a Dataset

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.


Accepted Solutions
Solution
‎08-12-2013 07:51 PM
Super Contributor
Posts: 307

Re: Evaluate Expression Stored in a Dataset

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;

View solution in original post


All Replies
Valued Guide
Posts: 3,208

Re: Evaluate Expression Stored in a Dataset

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. 

---->-- ja karman --<-----
Occasional Contributor
Posts: 9

Re: Evaluate Expression Stored in a Dataset

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.

Super Contributor
Posts: 307

Re: Evaluate Expression Stored in a Dataset

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

Valued Guide
Posts: 3,208

Re: Evaluate Expression Stored in a Dataset

@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 

---->-- ja karman --<-----
Super Contributor
Posts: 307

Re: Evaluate Expression Stored in a Dataset

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.

Valued Guide
Posts: 3,208

Re: Evaluate Expression Stored in a Dataset

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.

---->-- ja karman --<-----
Super User
Posts: 5,255

Re: Evaluate Expression Stored in a Dataset

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.

Data never sleeps
Super Contributor
Posts: 259

Re: Evaluate Expression Stored in a Dataset

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;

Occasional Contributor
Posts: 9

Re: Evaluate Expression Stored in a Dataset

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.

Respected Advisor
Posts: 3,777

Re: Evaluate Expression Stored in a Dataset

Occasional Contributor
Posts: 9

Re: Evaluate Expression Stored in a Dataset

Interesting. I will look into it.

Valued Guide
Posts: 3,208

Re: Evaluate Expression Stored in a Dataset

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;

---->-- ja karman --<-----
Solution
‎08-12-2013 07:51 PM
Super Contributor
Posts: 307

Re: Evaluate Expression Stored in a Dataset

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;

Occasional Contributor
Posts: 9

Re: Evaluate Expression Stored in a Dataset

Fugue,

Fantastic...This worked like a charm Smiley Happy

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 629 views
  • 6 likes
  • 6 in conversation