How to do a dynamic condition based on variable value

[Coooooo_Lee]

Hi there,

I have a datasets like below:

For each row, I want to check if the condition (variable CON) was met or not.
As you see, for different test the condition is different.

Here just part of data, it may have more than 100 different and conditions.

How should I structure a dynamic code to check each row based on the value of each CON?

Thanks!

[Reeza]

Data demo;
Set have end = eof;

If _n_=1 then do;
Call execute(‘data want; set have;’);
End;

Call execute (‘Value = ‘||cdn||’;’);

If eof then call execute(‘run’j;

Run;

What do you want as output? The T/F or 0/1?

call execute and dosubl are probably what you’ll need here. Resolve could also be an option but not sure. 

---

@Coooooo_Lee wrote:

Hi there,

I have a datasets like below:

For each row, I want to check if the condition (variable CON) was met or not.
As you see, for different test the condition is different.

Here just part of data, it may have more than 100 different and conditions.

How should I structure a dynamic code to check each row based on the value of each CON?

Thanks!

---

[Coooooo_Lee]

Hi Reeza, 

Thanks for the quick reply.

I'd like to have a dataset, which means if the condition was met then I can set a flag as 1, else flag=0.

Regarding below code, I think the second should be updated, right?

Call execute (‘Value = ‘||cdn||’;’);

A little bit confused by  ‘||cdn||’

[gamotte]

---

@Coooooo_Lee wrote:

Hi Reeza, 

Thanks for the quick reply.

I'd like to have a dataset, which means if the condition was met then I can set a flag as 1, else flag=0.

Regarding below code, I think the second should be updated, right?

Call execute (‘Value = ‘||cdn||’;’);

A little bit confused by  ‘||cdn||’

---

|| is a concatenation operator so the argument of call execute is the concatenation of strings :

- 'Value=',

- cdn,

- ';'

@Reeza's code will create a dataset with a new column 'Value' that corresponds to the flag you want to compute.

[ScottBass]

---

@Coooooo_Lee wrote:

Hi Reeza, 

Thanks for the quick reply.

I'd like to have a dataset, which means if the condition was met then I can set a flag as 1, else flag=0.

You weren't clear in your original post how you wanted to process your conditions.

If all you want to do is set a boolean flag perhaps this approach will work?

options mprint;

data tests;
   length con $100;
   infile datalines truncover;
   input con $100.;
   datalines4;
flag1 = (statecode = 'FL')
flag2 = (90000 <= zip <= 99999)
flag3 = (not missing(alias_city))
;;;;
run;

%macro code;
&con;
%mend;

data results;
   format flag1 flag2 flag3;  * set PDV order ;
   set sashelp.zipcode;
   %loop_control(control=tests)
run;

data flags;
   set results;
   if sum(of flag:) > 0;
   keep flag: statecode zip alias_city;
run;

%macro code;
%let num=&word;
data flag&num;
   set results;
   if flag&num=1;
   keep flag&num statecode zip alias_city;
run;
%mend;
%loop(1 2 3)

[s_lassen · Accepted solution]

How about this:

proc sql noprint;
  select distinct cats('when(',quote(trim(con),"'"),') check=(',con,')') into :check separated by ';' from have;
run;

data want;
  set have;
  select(con);
    ✓
  end;
run;

[hashman]

@s_lassen:

A fine solution, provided that the distinct conditions aren't numerous enough to make the value of CHECK exceed the maximum allowable macro variable length.  

Kind regards

Paul D.

[Coooooo_Lee]

Hi Reeza,

I guess something run here, I tested your code.

The log file shows like below:

1 + data want; set x;
2 + if PCHG <= -50 then fl="Y"; else fl="N";
3 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
4 + if CHG <= -4 then fl="Y"; else fl="N";
5 + if PCHG <= -50 then fl="Y"; else fl="N";
6 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
7 + if PCHG <= -50 then fl="Y"; else fl="N";
8 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
9 + if PCHG <= -50 then fl="Y"; else fl="N";
10 + if PCHG <= -50 then fl="Y"; else fl="N";
11 + if PCHG <= -50 then fl="Y"; else fl="N";
12 + if PCHG <= -50 then fl="Y"; else fl="N";
13 + if PCHG <= -50 then fl="Y"; else fl="N";
14 + if PCHG <= -50 then fl="Y"; else fl="N";
15 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
16 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
17 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
18 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
19 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
20 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
21 + if CHG <= -4 then fl="Y"; else fl="N";
22 + if CHG <= -4 then fl="Y"; else fl="N";
23 + if CHG <= -4 then fl="Y"; else fl="N";
24 + if CHG <= -4 then fl="Y"; else fl="N";
25 + if CHG <= -4 then fl="Y"; else fl="N";
26 + if CHG <= -4 then fl="Y"; else fl="N";
27 + if PCHG <= -50 then fl="Y"; else fl="N";
28 + if PCHG <= -50 then fl="Y"; else fl="N";
29 + if PCHG <= -50 then fl="Y"; else fl="N";
30 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
31 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
32 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
33 + if CHG <= -4 then fl="Y"; else fl="N";
34 + if CHG <= -4 then fl="Y"; else fl="N";
35 + if CHG <= -4 then fl="Y"; else fl="N";
36 + if PCHG <= -50 then fl="Y"; else fl="N";
37 + if PCHG <= -50 then fl="Y"; else fl="N";
38 + if PCHG <= -50 then fl="Y"; else fl="N";
39 + if PCHG <= -50 then fl="Y"; else fl="N";
40 + if PCHG <= -50 then fl="Y"; else fl="N";
41 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
42 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
43 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";
44 + if CHG <= -2 and AVAL in (0 1) then fl="Y"; else fl="N";

45 + run;

So, for each row, only the last logic would be checked.
What I want is check each condition on each row, which means checking PCHG <= -50 for the first row and CHG <= -2 and AVAL in (0 1) for the second row.

Is there a way?

[ScottBass]

As you see, for different test the condition is different.

No they're not.  Obs 8-13 have the same ID, TEST, and CON.  You have non-unique combinations of ID, TEST, and CON throughout your screenshot.

IMO, you should store the unique test conditions in one dataset, and the test results (this dataset) as a separate dataset.

My %loop_control macro may help here. 

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas

Since you only supplied a screenshot rather than example code, I can only provide dummy code:

%macro code;

   %let id=%trim(&id);

   %let test=%trim(&test);

   %let con=%trim(&con);

   data my_results;

      set my_input;

      if &con;

   run;

   title1 "ID: &id";

   title2 "TEST: &test";

   proc print data=my_results;

   run;

   title;

%mend;

%loop_control(control=<your unnamed dataset from your screenshot>)

See the macro header for more use cases.

[PeterClemmensen]

Here is a Dosubl approach

data have;
input id test $ con :$100. chg pchg aval;
infile datalines dlm=',';
datalines;
2001,E,PCHG <= -50,-8,-20.05012531,31.9
2001,I,CHG <= -2 and AVAL in (0 1),0,0,3
;

data want(drop=i rc);
    do i=1 to nobs;
       set have nobs=nobs;
       rc=dosubl(compbl(cat("
          data _null_; 
             n=", i, "; 
             set have point=n; 
             x=(", con, ");
             call symputx('flag', x);
             stop;
          run;")));
       flag=input(symget('flag'), 8.);
       output;
    end;
run;

[Ksharp]

Reeza's code could change into :

data have;
input id test $ con :$100. chg pchg aval;
infile datalines dlm=',';
datalines;
2001,E,PCHG <= -50,-8,-20.05012531,31.9
2001,I,CHG <= -2 and AVAL in (0 1),0,0,3
;
data _null_;
 set have end=last;
 if _n_=1 then call execute('data want;set have;');
 call execute(catt('if _n_=',_n_,' then flag=(',con,');'));
 if last then call execute('run;');
  else call execute('else ');
run;

[mkeintz]

I think @Ksharp's suggestion is the most appropriate for reasonably small number of rows.  But for N=1,000,000, there will be an average of 500,000 IF tests per observation -- even if you might only have, say, 100 unique CON expressions.

In that case, it's better to construct something like;

   select (con);

     when ("x>1")   flag=(x>1);

     when ("chg*x>3") flag=(chg*x>3);

  end;

You can do that by writing to a temporary ascii file holding code such as my example above and then %INCLUDE it in a "data want;" step, as in:

filename tmp clear;
filename tmp temp;
data _null_;
  if 0 then set have (keep=con);
  declare hash h (dataset:'have (keep=con)');
    h.definekey('con');
    h.definedata('con');
    h.definedone();
  declare hiter hi ('h');
  file tmp;
  do rc=hi.first() by 0 until (hi.next()^=0);
    put '  when ('  con $quote. ') flag=(' con ');' ;
  end;
run;
data want;
  set have ;
  select (con);
  %include tmp /source2;
  end;
run;

The key to the above is the creation of a hash object with CON as its key.  The object will default to one item in the object per unique key.  The program constructs such an object (named H), and then iterates through it to write out code as I posted above.

If you are uncomfortable using hash object, then you can use a sorted (by CON) dataset, as here:

proc sort data=have (keep=con) out=sorted_con nodupkey;
  by con;
run;

filename tmp temp;
data _null_;
  set sorted_con;
  file tmp;
  put '  when ('  con $quote. ') flag=(' con ');' ;
run;

data want;
  set have;
  select (con);
  %include tmp / source2;
  end;
run;

Again, this is beneficial (in terms of performance speed) only when the cardinality of CON values is significantly smaller (maybe ration of 1/10 or so?) than the number of observations in HAVE.

In both case, you would be using a DATA _NULL_ step, which allow you to SET a dataset, but not bother writing out a sas dataset.  And that's what you want, because the purpose here is to write out all the "when (...) flag=(...):" statements to an ascii file.