topic Re: Creating and resolving macro variables as a validation rule in the same data step in SAS Programming

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

Alkibiades — Fri, 21 Sep 2018 14:54:47 GMT

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?

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

gamotte — Fri, 21 Sep 2018 15:13:11 GMT

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;

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

novinosrin — Fri, 21 Sep 2018 16:01:38 GMT

Did the hash solution work?

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

Alkibiades — Fri, 21 Sep 2018 16:15:57 GMT

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

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

Shmuel — Sun, 23 Sep 2018 08:13:12 GMT

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.

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

Alkibiades — Sun, 23 Sep 2018 09:01:09 GMT

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.