DATA Step, Macro, Functions and more

Create and use columns as macrovariables within datastep

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Create and use columns as macrovariables within datastep

Hi, I'm dealing with the following scenario:

I have a table RULES which contains two character fiels: CONDITION and DESCRIPTION:

RULECONDITIONDESCRIPTION
RULE1COL1 < 0Warning, COL1 CAN'T BE NEGATIVE (current value = &COL1)
RULE2COL1 + COL2 > 100Warning, THE SUM COL1+COL2 IS INVALID (current values: &COL1 + &COL2 > 100)

Next, I have a table EVENTS which contains some variables:

COL1COL2COL3
804010
-10100

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:

COL1COL2COL3RULE1RULE2RULE1_DESCRIPTIONRULE2_DESCRIPTION
80401001okWarning, THE SUM COL1+COL2 IS INVALID (current values: 80 + 40 > 100)
-1010010Warning, 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


Accepted Solutions
Solution
‎08-25-2015 02:32 PM
Contributor ndp
Contributor
Posts: 61

Re: Create and use columns as macrovariables within datastep

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

Re: Create and use columns as macrovariables within datastep

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;

Regular Contributor
Posts: 200

Re: Create and use columns as macrovariables within datastep

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

Solution
‎08-25-2015 02:32 PM
Contributor ndp
Contributor
Posts: 61

Re: Create and use columns as macrovariables within datastep

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;

Super User
Posts: 9,687

Re: Create and use columns as macrovariables within datastep

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

Contributor
Posts: 43

Re: Create and use columns as macrovariables within datastep

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 360 views
  • 6 likes
  • 5 in conversation