- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The %eval and %sysevalf functions might do the trick for you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
DOSUBL looks about right.
http://www.lexjansen.com/pharmasug/2013/AD/PharmaSUG-2013-AD24-SAS.pdf
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Interesting. I will look into it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;