Creating and resolving macro variables as a validation rule in the same data step

[Alkibiades]

Okay, I have a tricky issue and hope for some help.

I have two datasets, one (data1) which has values as data and one (data2) which has field names and relational operators which whom I want to check the data in the first dataset.

For instance:

Dataset data1:

obs   Field1    Field2    Field3

1           3           5             UT

2           0           12           XX3

...         

Dataset data2:

obs rule_no operand1   rel_oper     operand2

1     r1          Field1            >                  0

2     r2          Field3            =                 UT

3     r3          Field1           <=              Field2

...

I seek to execute the validation rules of data2 on data1 and thus have to resolve operand1, rel_oper and operand2 on the fly if they are fieldnames of data1.

For instance if I wand to check the rule in obs 3 of data2 on ob1 of data1 I would expect:

3 <= 5

My first idea was to use a hash to lookup on the validation rules like:

data test;

attrib ...;
if _n_ eq 1 then do
    dcl hash data2(dataset: 'data2')
    data2.definekey('rule_no');
    data2.definedata('operand1','rel_oper','operand2');
    data2.definedone();

    call missing(operand1,rel_oper,operand2);
end;

set data1;

rule_no = r3;

rc = data2.find();

now I somehow try to manage to resolve the value of the field name I get from the hash.

run;

Maybe there is a better Solution to my problem?

[gamotte · Accepted solution]

Please provide data in the form of an executable data step.

The function you are looking for is call execute that allows to generate SAS

code from the columns of a dataset.

Here is a (not  tested) proposition for your problem.

data _NULL_;
    call execute('data want; set data1;')

    do until(eof);
        set data2 end=eof;
        call execute(cat('if ',operand1,rel_oper,operand2,' then ',obs_rule_no,'="OK"'));
        call execute(cat('else ',obs_rule_no,'="KO"'));
    end;

    call execute('run');

    stop;
run;

[novinosrin]

Did the hash solution work?

[Alkibiades]

I'm very eager to test it, but I won't be able to test it until Monday.

[Shmuel]

Should all rules in data2 be checked on all observations of data1?

What should be the result if some rules are OK and other NOT?

You can use the call execute, as suggested by @gamotte, maybe need some adaption,

or generate a program (data step) from the rules data - can be done pro-grammatically using FILE & PUT statements,

thus more easy to check, debug and generated program can be saved.

[Alkibiades]

I indeed missed that, good point, thank you.

Not all rules should be checked, just a subset for certain customer.

I'll expand my example:

Dataset data1:

obs   customer_no   Field1    Field2    Field3

1           a11                 3           5             UT

2           b34                 0           12           XX3

...         

Dataset data2:

obs customer_no   rule_no operand1   rel_oper     operand2

1            b34               r1          Field1            >                  0

2            a11               r2          Field3            =                 UT

3            a11               r3          Field1           <=              Field2

I was thinking of utilizing a hash for this.

Then I even pondered using ds2, the only issue with ds2 is, I cannot hand over values to be used as field names themselves who then reference to the value I want to check. Or handing over values as macro variables to be used in the invoked method as field names like in a macro. If that was possible somehow, it could be a very elegant solution.