BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SelvaN
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Fugue
Quartz | Level 8

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

14 REPLIES 14
jakarman
Barite | Level 11

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 --<-----
SelvaN
Fluorite | Level 6

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
Quartz | Level 8

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

jakarman
Barite | Level 11

@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 --<-----
Fugue
Quartz | Level 8

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
Barite | Level 11

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 --<-----
LinusH
Tourmaline | Level 20

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
andreas_lds
Jade | Level 19

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
Fluorite | Level 6

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.

SelvaN
Fluorite | Level 6

Interesting. I will look into it.

jakarman
Barite | Level 11

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 --<-----
Fugue
Quartz | Level 8

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
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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