topic Re: apply formula that in data set in SAS Programming

apply formula that in data set

Ronein — Tue, 17 Dec 2024 07:57:27 GMT

Hello

Let's say that there is a data set with required formula to apply.

What is the way to appy the formula that in the data set on other data set?

Data have;
input x y z;
cards;
10 20 30
15 20 200
10 30 40
;
run;

data formula_tbl;
input formula $;
cards;
x+y-0.1*x
;
run;

proc sql;
create table t1 as
select *
from have,formula_tbl
;
quit;

data want;
set t1;
calc=formula;
run;

Re: apply formula that in data set

LinusH — Tue, 17 Dec 2024 08:04:43 GMT

The goto method of generating dynamic logic in SAS is by using macros, and that should work fine for this simple scenario.

So read your data set formula and push the formula to a macro variable by using CALL SYMPUT.

My guess is that your real life scenario is slightly more complicated/extensive, so I need to see more to determine if CALL SYMPUT would work.

Re: apply formula that in data set

Patrick — Tue, 17 Dec 2024 08:49:04 GMT

A common method involves generating the SAS code, writing it to an external file, and then including this file in the subsequent data step for execution.

Although the data _null_ step with the put statement and the necessary quoting can become somewhat cumbersome, I appreciate this approach because the include statement enables you to write the generated code to the SAS log exactly where it is executed. This makes troubleshooting and debugging significantly easier.

Data have;
	input x y z;
	cards;
10 20 30
15 20 200
10 30 40
;
run;

data formula_tbl;
	infile datalines dsd dlm='~';
	input target_var :$32. expression :$100.;
	cards;
calc~x+y-0.1*x
;
run;

filename codegen temp;
data _null_;
/* 	file print; */
	file codegen;
	set formula_tbl;
	put target_var '=' expression ';';
run;

data want;
	set have;
	%include codegen /source2;
run;

Re: apply formula that in data set

whymath — Tue, 17 Dec 2024 08:49:03 GMT

There is an blog article from Yinliang Wu: https://blogs.sas.com/content/sgf/2021/06/25/how-to-evaluate-sas-expression-in-data-step-dynamically/

Re: apply formula that in data set

yabwon — Tue, 17 Dec 2024 12:09:59 GMT

This might help:

https://communities.sas.com/t5/Polish-SAS-Users-Group/Kolejny-pow%C3%B3d-by-pokocha%C4%87-hash-tablice/m-p/640297#M22

The comment is in Polish but I'm sure google translator will help you. And the code is in SAS so you should be able to understand it.

Bart

Re: apply formula that in data set

yabwon — Tue, 17 Dec 2024 12:13:54 GMT

code for your case:

Data have;
input x y z;
cards;
10 20 30
15 20 200
10 30 40
;
run;

data formula_tbl;
input formula $ 12.;
cards;
x+y-0.1*x
;
run;


options dlcreatedir;
libname x "%sysfunc(pathname(work))\spde\";
libname x SPDE "%sysfunc(pathname(work))\spde\";

proc sql;
create table x.testc as
select have.*, formula as CODE
from have,formula_tbl
;
quit;






/* options nonotes; */ /* odkomentuj zeby miec czysty log */
data testc3;
  set 
    x.testc 
  curobs=curobs indsname=indsname end=end
  ;

  if missing(code) then 
    do;
      output;
    end;
  else
    do;
      length strX $ 1024;
      strX = cat(
        strip(indsname)
        , '(startobs = ', curobs  /* <-- */
        , '   endobs = ', curobs  /* <-- */
        , '    where = (', strip(code), '))' 
        );
      declare hash H(dataset:strX);
      rc = H.defineKey("code");
      rc = H.defineDone();
      if H.num_items > 0 then output;
      rc = H.delete();
    end;

  if end then put _N_=;
run;
/*options notes;*/

Re: apply formula that in data set

Ronein — Tue, 17 Dec 2024 18:31:02 GMT

Is there limit of length thst macro var can store?

Re: apply formula that in data set

Tom — Tue, 17 Dec 2024 18:55:46 GMT

@Ronein wrote:
Is there limit of length thst macro var can store?

Yes. 64K Bytes.

That is another reason why using a data step to write the code to a file is a better solution for code generation problems like this.

Re: apply formula that in data set

Ksharp — Wed, 18 Dec 2024 03:00:56 GMT

Data have;
input x y z;
cards;
10 20 30
15 20 200
10 30 40
;
run;

data formula_tbl;
input formula $20.;
cards;
x+y-0.1*x
x+y+z
;
run;

proc sql;
create table t1 as
select *
from have,formula_tbl
;
quit;

data want;
set t1;
length _formula $ 200;
_formula=formula;
array _x{*} _numeric_;
do i=1 to dim(_x);
 _formula=tranwrd(lowcase(_formula),lowcase(vname(_x{i})),strip(_x{i}));
end;
result=input(resolve(cats('%sysevalf(',_formula,')')),best32.);
drop i _formula;
run;

Re: apply formula that in data set

yabwon — Wed, 18 Dec 2024 15:40:30 GMT

One more approach:

proc sql;
create table testc as
select have.*, formula as CODE
from have,formula_tbl
;
quit;

proc sql;
  create table code_b as 
  select distinct QUOTE(strip(code)) as q, count(code) as cc
  from testc
  order by cc desc ;
  ; 
quit;

filename testb2 TEMP lrecl=2000;

data _null_;
  file testb2;
  length _X_ $ 2000;

  put "data testb2;";
  put "set testc;";
  put "select;";
  do until(EOF);
    set code_b end=EOF;
    _X_ = "when (code = " 
        || strip(q)
        || ") do; result = (" 
        || dequote(q)
        || "); end;"; 
    put _X_;
  end;
  put "otherwise;";
  put "end;";
  put "run;";
stop;
run;

%include testb2 / SOURCE2;
filename testb2;
proc print data=testb2;
run;

Re: apply formula that in data set

Ksharp — Thu, 19 Dec 2024 01:37:29 GMT

One more approach:

Especially for the formula which is unable to handle by %sysevalf().

Data have;
input x y z;
cards;
10 20 30
15 20 200
10 30 40
;
run;

data formula_tbl;
input formula $20.;
cards;
x+y-0.1*x
x+y+z
;
run;

proc sql;
create table t1 as
select *
from have,formula_tbl
;
quit;

filename x temp;
data _null_;
 set t1 end=last;
 file x;
 if _n_=1 then put 'data want; set t1;';
 put 'if _n_=' _n_ 'then result=' formula ';';
 if last then put 'run;';
run;
%include x;

Re: apply formula that in data set

Tom — Thu, 19 Dec 2024 16:12:02 GMT

Don't use STRIP if the intent is to generate a quoted string that represent the actual value of a variable. It will remove the leading spaces so the resulting strings are NOT the same.

To make the quoted string shorter just use TRIM() as trailing spaces are not significant.

Example:

data have;
  code = '   xxx    ';
run;

proc sql noprint;
  select quote(trim(code),"'")
       , quote(strip(code),"'") 
    into :good trimmed
       , :bad trimmed
  from have
  ;
quit;

data test;
  set have;
  if code = &good then put "GOOD string &good matched " code= :$quote.;
  else  put "GOOD string &good NOT matched " code= :$quote.;
  if code = &bad  then put "BAD string &bad matched " code= :$quote.;
  else put "BAD string &bad NOT matched " code= :$quote.;
run;

Results

295  data test;
296    set have;
297    if code = &good then put "GOOD string &good matched " code= :$quote.;
298    else  put "GOOD string &good NOT matched " code= :$quote.;
299    if code = &bad  then put "BAD string &bad matched " code= :$quote.;
300    else put "BAD string &bad NOT matched " code= :$quote.;
301  run;

GOOD string '   xxx' matched code="   xxx"
BAD string 'xxx' NOT matched code="   xxx"
NOTE: There were 1 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.TEST has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: apply formula that in data set

quickbluefish — Thu, 19 Dec 2024 19:01:54 GMT

I think the difficulty with this question is that you've actually created an over-simplified example of what you actually need. Here, you've got a formula table that you're joining (in a Cartesian way) to your data, but the formula table only, in this example, contains a single formula, so it's not clear how the join would be modified if it contained multiple formulas that would be used conditionally based on, I guess, the data itself. It would be easier for people to respond if your example formula table contained at least 2 different formulas and that the data step that followed showed us how you would use those formulas.

In any case, I'm guessing you have a programming background that is maybe something other than SAS - nothing wrong with that - just saying that the way you're approaching this is very unusual and probably much more complicated than it needs to be.

With your current example, you could just skip the formula table (and the join) entirely and just run a macro, e.g.:


%macro xy;
    x * y - 1
%mend;

data want;
set have;  
calc=%xy;
run;

...but again, if you provide a more realistic example, we might be able to give you a better answer.