BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrom34
Calcite | Level 5
Hi,

I have a table with 2 columns. One is simple formula and the other one a numeric variable. I am trying to resolve the formula in the dataset but it does not work and I am not sure to understand why. Below is the example:


DATA temp1;
INPUT formula $ var1 ;
DATALINES;
2*var1 1
23*var1 2
12*var1 5
;


data temp2;
set temp1;
format result 8.;
result=formula;
run;

Why is it generating an error? if ifdo something like this, it is perfectly fine.
data temp2;
set temp1;
format result 8.;
result=var1*5;
run;


Thnaks a lot
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Hi.
If you have very large dataset, use the tranwrd function and resolve function with %sysevalf :

DATA temp1;
INPUT formula $ var1 ;
DATALINES;
2*var1 1
23*var1 2
12*var1 5
; 
data result;
 set temp1;
 temp=tranwrd(formula ,'var1',strip(var1));
 result=resolve('%sysevalf('||temp||')');
run;



If you have not very large dataset, CALL EXECUTE can generate the statements for each record to calculate the result:

 

DATA temp1;
INPUT formula $ var1 ;
DATALINES;
2*var1 1
23*var1 2
12*var1 5
; 

data _null_;
 set temp1 end=last;
 if _n_=1 then call execute('data _result;');
 call execute('var1='||var1||';');
 call execute('formula="'||formula||'";');
 call execute('result='||formula||';');
 call execute('output;');
 if last then call execute('run;');
run;


Ksharp

View solution in original post

4 REPLIES 4
RickM
Fluorite | Level 6
Your variable Formula is only a character variable.

This means for the first observation in your second dataset is equivalent to

result="2*var1";

which is not the same as

result=2*var1;

Only macro variables resolve the way that you are thinking. You should take another look at what you are trying to accomplish and try to figure out a simpler solution.
bhavani
Calcite | Level 5
Assuming that you will always multiply with Var1.

Strip the first value before the '*' in Formula and then use it to multiply with Var1.

DATA temp1;
INPUT formula $ var1 ;
DATALINES;
2*var1 1
23*var1 2
12*var1 5
;
run;

data temp2;
set temp1;
format result 8.;
*result=formula;
result=input(scan(formula,1,'*'),best.) * Var1;
run;

proc print data=temp2;
run;

Obs formula var1 result

1 2*var1 1 2
2 23*var1 2 46
3 12*var1 5 60

When you assign Formula to Result, you are merely copying over the value to Result.
I don't think you can resolve a formula in a datastep. Message was edited by: bhavani
polingjw
Quartz | Level 8
See this posting from a couple of months ago. Chang showed a solution here that proves it is indeed possible to resolve a formula in a datastep.

http://support.sas.com/forums/thread.jspa?messageID=47101럽
Ksharp
Super User

Hi.
If you have very large dataset, use the tranwrd function and resolve function with %sysevalf :

DATA temp1;
INPUT formula $ var1 ;
DATALINES;
2*var1 1
23*var1 2
12*var1 5
; 
data result;
 set temp1;
 temp=tranwrd(formula ,'var1',strip(var1));
 result=resolve('%sysevalf('||temp||')');
run;



If you have not very large dataset, CALL EXECUTE can generate the statements for each record to calculate the result:

 

DATA temp1;
INPUT formula $ var1 ;
DATALINES;
2*var1 1
23*var1 2
12*var1 5
; 

data _null_;
 set temp1 end=last;
 if _n_=1 then call execute('data _result;');
 call execute('var1='||var1||';');
 call execute('formula="'||formula||'";');
 call execute('result='||formula||';');
 call execute('output;');
 if last then call execute('run;');
run;


Ksharp

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 4 replies
  • 2293 views
  • 5 likes
  • 5 in conversation