topic Create and use columns as macrovariables within datastep in SAS Programming

Create and use columns as macrovariables within datastep

Edoedoedo — Mon, 24 Aug 2015 14:03:02 GMT

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

Re: Create and use columns as macrovariables within datastep

RW9 — Mon, 24 Aug 2015 14:14:07 GMT

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;

Re: Create and use columns as macrovariables within datastep

Ron_MacroMaven — Tue, 25 Aug 2015 14:55:07 GMT

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

Macro CallMacr - sasCommunity

Re: Create and use columns as macrovariables within datastep

ndp — Tue, 25 Aug 2015 18:32:17 GMT

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;

Re: Create and use columns as macrovariables within datastep

Ksharp — Wed, 26 Aug 2015 02:13:17 GMT

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

Re: Create and use columns as macrovariables within datastep

Edoedoedo — Thu, 27 Aug 2015 15:46:14 GMT

Thanks a lot guys, call execute without macrovariables works fine, it's simple and elegant.