Hi, I'm dealing with the following scenario:
I have a table RULES which contains two character fiels: CONDITION and DESCRIPTION:
RULE | CONDITION | DESCRIPTION |
---|---|---|
RULE1 | COL1 < 0 | Warning, COL1 CAN'T BE NEGATIVE (current value = &COL1) |
RULE2 | COL1 + COL2 > 100 | Warning, THE SUM COL1+COL2 IS INVALID (current values: &COL1 + &COL2 > 100) |
Next, I have a table EVENTS which contains some variables:
COL1 | COL2 | COL3 |
---|---|---|
80 | 40 | 10 |
-10 | 10 | 0 |
Note that in RULES.CONDITION I have explicitly the EVENTS columns name; in RULES.DESCRIPTION I have the EVENTS columns name with "&" prefixed.
I want to create an OUTPUT table as follows:
COL1 | COL2 | COL3 | RULE1 | RULE2 | RULE1_DESCRIPTION | RULE2_DESCRIPTION |
---|---|---|---|---|---|---|
80 | 40 | 10 | 0 | 1 | ok | Warning, THE SUM COL1+COL2 IS INVALID (current values: 80 + 40 > 100) |
-10 | 10 | 0 | 1 | 0 | Warning, COL1 CAN'T BE NEGATIVE (current value = -10) | ok |
RULE1/2 are easy (I'm using call execute, I sketch the code so there may be mistakes it is just to point the idea out):
data _NULL_;
set RULES;
if (_N_ = 1) then call execute('data OUTPUT; set EVENTS;);
call execute(RULE || '= ifn(' || CONDITION || ',1,0);');
.......
run;
This works perfectly. Now I want to add description, so I tried as before:
call execute(RULE || '_DESCRIPTION = ifn(' || CONDITION || ',' || DESCRIPTION || ','ok);');
but this does not resolve macrovariables (of course because they don't exists), so I get e.g. Warning, COL1 CAN'T BE NEGATIVE (current value = &COL1).
But if I try do define them using symput in the same datastep of course I get wrong values.
How would you do that in a simple way?
Thanks a lot
no need to create and use macro variables:
data rules;
length condition $25 description $200;
rule="RULE1";
condition="COL1 < 0";
description="'Warning, COL1 CANT BE NEGATIVE (current value = '||STRIP(PUT(COL1,BEST.))||')'";
output;
rule="RULE2";
condition="COL1 + COL2 > 100";
description="'Warning, THE SUM COL1+COL2 IS INVALID (current values: '||STRIP(PUT(COL1,BEST.))||' + '||STRIP(PUT(COL2,BEST.))||' > 100)'";
output;
run;
data _null_;
set rules end=eof;
if (_n_=1) then call execute('data output; set evnt;');
call execute(rule||'=ifn('||strip(condition)||',1,0);');
/* call execute(desc||'=ifn('||strip(rule)||','||desc||',ok);');*/
call execute(rule || "_description = ifc(" || strip(condition) || "," || strip(description) || ",'Ok');");
if eof then call execute('run;');
run;
Yes, you have to remember the order of execution in this instance. The call execute generates code which then goes to the compiler, it doesn't then go back through the pre-processor. Instead of puting &COL1 in your description, have some sort of easy to find replacing string, e.g.:
Warning, COL1 CAN'T BE NEGATIVE (current value = ~COL1~)
Then have a post-check processing datastep which merely replaces values:
data final;
set check_output;
reason=tranwrd(reason,"~COL1~",strip(put(col1,best.)));
reason=tranwrd(reason,"~COL2~",strip(put(col2,best.)));
run;
That's probably the simplest way. You could also modify your code to call a macro with the data:
data _null_;
set checks;
call execute('%my_macro (col1='||put(col1,best.)||', col2='||put(col2,best.)||', check="'||check||'");');
run;
Here are two pages with programs that address your issues:
how to use a list, a control data set,
to call another program.
http://www.sascommunity.org/wiki/Call_Execute_Parameterized_Include
no need to create and use macro variables:
data rules;
length condition $25 description $200;
rule="RULE1";
condition="COL1 < 0";
description="'Warning, COL1 CANT BE NEGATIVE (current value = '||STRIP(PUT(COL1,BEST.))||')'";
output;
rule="RULE2";
condition="COL1 + COL2 > 100";
description="'Warning, THE SUM COL1+COL2 IS INVALID (current values: '||STRIP(PUT(COL1,BEST.))||' + '||STRIP(PUT(COL2,BEST.))||' > 100)'";
output;
run;
data _null_;
set rules end=eof;
if (_n_=1) then call execute('data output; set evnt;');
call execute(rule||'=ifn('||strip(condition)||',1,0);');
/* call execute(desc||'=ifn('||strip(rule)||','||desc||',ok);');*/
call execute(rule || "_description = ifc(" || strip(condition) || "," || strip(description) || ",'Ok');");
if eof then call execute('run;');
run;
You can trasform it into values before call execute .
Like something.
call execute('temp=tranwrd(" '||DESCRIPTION||' ","&COL1", COL1); ' );
call execute('temp=tranwrd(" '||DESCRIPTION||' ","&COL2", COL2); ' );
call execute('temp=tranwrd(" '||DESCRIPTION||' ","&COL3", COL3); ' );
call execute(RULE || '_DESCRIPTION = ifn(' || CONDITION || ',temp,"ok");');
消息编辑者为:xia keshan
Thanks a lot guys, call execute without macrovariables works fine, it's simple and elegant.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.