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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.