topic Re: Macro loop problem in SAS Programming

Macro loop problem

forumsguy — Tue, 16 Jul 2013 03:31:10 GMT

Guys, I am doing a concatenation reading data one observation at a time. So my data and code is as follows:

data have;

input @1 label1 $ @3 label2 $ @5 label3 $1. @7 condition $;

cards;

a d p code1

a d p code7

b e q code2

b e q code3

c p code4

d a r code5

d a r code6

;

run;

options nomlogic ;

%macro rules (table_name);

/* open the table*/

%let table_id=%sysfunc(open(&table_name,i));

/* fetch the first record */

%let rc=%sysfunc(fetch(&table_id));

%if &rc ne 0 %then

%do;

%put something is wrong ...;

%end;

%else

%do;

/* read data for first record */

%let label1=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,label1))));

%let label2=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,label2))));

%let label3=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,label3))));

%let condition=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,condition))));

/* Save values and iterate again */

%let prev_label1=&label1;

%let prev_label2=&label2;

%let prev_label3=&label3;

%let prev_condition=&condition;

/* Read remaining records */

%let rc=%sysfunc(fetch(&table_id));

%do %while (&rc eq 0);

%let label1=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,label1))));

%let label2=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,label2))));

%let label3=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,label3))));

%let condition=%sysfunc(getvarc(&table_id,

%sysfunc(varnum(&table_id,condition))));

%if (&prev_label1 eq &label1)and (&prev_label2 eq &label2) and (&prev_label3 eq &label3) %then

%do;

%let new_condition=case when &prev_condition or (&condition);

%put new_condition is &new_condition;

%end;

%else %do;

%let prev_label1=&label1;

%let prev_label2=&label2;

%let prev_label3=&label3;

%let prev_condition=&condition;

%let new_condition=case when &prev_condition or (&condition);

%end;

%put new_condition is &new_condition;

%let rc=%sysfunc(fetch(&table_id));

%end;

%let rc=%sysfunc(close(&table_id));

%end;

%mend;

%rules(have);

What i want in output is

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

However, my last observation and first observation gets lost in loop. Please share your views.. Thanks in advance

Re: Macro loop problem

Peter_C — Tue, 16 Jul 2013 07:18:29 GMT

Why force it into the macro language?

Re: Macro loop problem

forumsguy — Tue, 16 Jul 2013 08:37:01 GMT

@Peter C.. Thats my requirement. This is prod code and cant be changed.. Hence i am stuck with this.. :smileycry:

Re: Macro loop problem

Peter_C — Tue, 16 Jul 2013 10:00:54 GMT

it would be easier to do the same thing in a data step.

If it is prod code why does it need to change?

My suggestion is

Data null ;

Call execute( '%nrstr( %%p)ut new condition is case when ( ' ) ;

Do until( last.label3) ;

Set your.data;

By label1 label2 label3 ;

Call execute( '( ' !! condition !! ' ) ' ) ;

If not last.label3 then

Call execute( ' or ' );

End ;

Call execute( ' ) ' ) ;

Run;

Re: Macro loop problem

Tom — Tue, 16 Jul 2013 13:23:53 GMT

Look into using %SYSCALL SET(...) statement. This will eliminate the need to pull the variable values into macro variables.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Re: Macro loop problem

Vince28_Statcan — Tue, 16 Jul 2013 13:47:35 GMT

Hi Forumsguy,

I am not sure this is what you are looking for based on the output you said you wanted, however, I assumed from the code that your intentions were cases where all 3 labels are identical to be output on a single line and only once. I simply added a tracker of "single codes" and slightly adapted your %put statements.

However, if what I did was not what you were looking for, here is some clues as to why your output is odd. The %put outside at the end of your do while effectively duplicates all %put in the case where labels are equal. If it is your way around to douple parse such cases, you may as well just duplicate the put statement in your do segment rather than use one outside. This will allow you to control the %else statement with a boolean-like variable telling you if you were ongoing a "single label" versus a pair (or more) of labels. In other words, it would be best if you had your %put statements each within the do/else and not one outside.

I am not entirely sure why you mentionned you were "losing" the first and last record of your DS with the example you've mentionned as to what output you really want.

If you wanted an output like

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

That is, effectively duplicating all including first and last observations, you can simply copy past the %put statement in the labels are equal %do segment in the code I provided.

Hope this helps.

Vincent

Re: Macro loop problem

Tom — Tue, 16 Jul 2013 14:24:09 GMT

Looks like you are trying to mimic FIRST. processing in macro code?

Try this.

data have;

input label1 $ label2 $ label3 $ condition $ ;

cards;

a d p code1

a d p code7

b e q code2

b e q code3

c p . code4

d a r code5

d a r code6

;

run;

%macro rules (table_name);

%local label1 label2 label3 condition ;

%local prev_label1 prev_label2 prev_label3 prev_condition ;

%local table_id rc n first new_condition ;

/* open the table */

%let table_id=%sysfunc(open(&table_name,i));

%if not &table_id %then %do;

%put Unable to open &table_name ;

%goto quit;

%end;

/* Link dataset variables to matching macro variables */

%syscall set(table_id);

/* Read all observations */

%let n=0;

%do %while (not %sysfunc(fetch(&table_id)));

%let n=%eval(&n + 1);

%let first=%eval( &n=1

or %superq(prev_label1) ne %superq(label1)

or %superq(prev_label2) ne %superq(label2)

or %superq(prev_label3) ne %superq(label3)

)

;

/* Build up new_condition */

%if &first %then %let new_condition=&condition;

%else %let new_condition=&new_condition or &condition ;

%put n=&n first=&first new_condition is case when (&new_condition) ;

/* Save values for next iteration */

%let prev_label1=%superq(label1);

%let prev_label2=%superq(label2);

%let prev_label3=%superq(label3);

%let prev_condition=%superq(condition);

%end;

%let rc= %sysfunc(close(&table_id));

%quit:

%mend;

%rules(have);

n=1 first=1 new_condition is case when (code1)

n=2 first=0 new_condition is case when (code1 or code7)

n=3 first=1 new_condition is case when (code2)

n=4 first=0 new_condition is case when (code2 or code3)

n=5 first=1 new_condition is case when (code4)

n=6 first=1 new_condition is case when (code5)

n=7 first=0 new_condition is case when (code5 or code6)

Re: Macro loop problem

Tom — Tue, 16 Jul 2013 14:41:07 GMT

Adding both FIRST. and LAST. is a little harder.

Try this. I also added () around the values of the CONDITION variable.

%macro rules (table_name);

%local label1 label2 label3 condition ;

%local prev_label1 prev_label2 prev_label3 ;

%local table_id rc n first new_condition ;

/* open the table */

%let table_id=%sysfunc(open(&table_name,i));

%if not &table_id %then %do;

%put Unable to open &table_name ;

%goto quit;

%end;

/* Link dataset variables to macro variables */

%syscall set(table_id);

%let n=0;

%do %while (not %sysfunc(fetch(&table_id)));

%let n=%eval(&n + 1);

%let first=%eval( &n=1

or %superq(prev_label1) ne %superq(label1)

or %superq(prev_label2) ne %superq(label2)

or %superq(prev_label3) ne %superq(label3)

)

;

/* Build up new_condition and emit previous condition */

%if &first %then %do;

%if (&n > 1) %then %put new_condition is case when (&new_condition) ;

%let new_condition=(%trim(&condition));

%end;

%else %let new_condition=&new_condition or (%trim(&condition)) ;

/* Save values for next iteration */

%let prev_label1=%superq(label1);

%let prev_label2=%superq(label2);

%let prev_label3=%superq(label3);

%end;

%let rc= %sysfunc(close(&table_id));

%if (&n) %then %put new_condition is case when (&new_condition) ;

%else %put No Conditions;

%quit:

%mend;

%rules(have);

new_condition is case when ((code1) or (code7))

new_condition is case when ((code2) or (code3))

new_condition is case when ((code4))

new_condition is case when ((code5) or (code6))

Re: Macro loop problem

forumsguy — Tue, 16 Jul 2013 15:24:06 GMT

@TOM.. Amazing... its working .. However, it gives me one minor error....

The keyword parameter was not defined with the macro... do you have idea what it could be for ?? I guess its related to SAS installation folder

Re: Macro loop problem

forumsguy — Tue, 16 Jul 2013 15:32:05 GMT

Tom...Got it corrected.. Guess I need to read on Syscall set ... it seems magical ....thanks a lot guys ..so many ways to solve the problem.... Awesome...

Re: Macro loop problem

ballardw — Tue, 16 Jul 2013 15:32:08 GMT

Errors related to keyword parameter not defined usually point to a call in a macro like

%dummy(parm1=somevalue), but when the macro was defined as in %macro dummy([parameters]) did not include parm1 with the = . Without the = the parameter is positional and not referenced as (parm1=).

If you tried calling Tom's macro with %rules(table_name=) that would be the cause.

Re: Macro loop problem

Tom — Tue, 16 Jul 2013 15:33:34 GMT

Most likely there are equal signs in CONDITION and it is confusing to call to %TRIM().

Convert

%trim(&condition)

&condition

or to

%trim(%superq(condition))

Re: Macro loop problem

Peter_C — Tue, 16 Jul 2013 19:37:55 GMT

Pity a data step isn't an option. It protects so well against values like condition becoming confused with process.

Re: Macro loop problem

forumsguy — Wed, 17 Jul 2013 14:28:27 GMT

@Tom.. There is also one dot in my condition. How should I resolve it .. One of my conditions is

val_write_off ^=.

Since superq doesnot unmask it .. is there any other way.. I tried with other functions as well..However couldnt find out solution....

Re: Macro loop problem

Peter_C — Wed, 17 Jul 2013 14:52:39 GMT

Use missing() function

Use

Not column is null

Re: Macro loop problem

Tom — Wed, 17 Jul 2013 22:23:11 GMT

Why not just give up on that old %TRIM() macro and use %SYSFUNC() to call the TRIM() function. That will not care about equal signs or periods. If the value of CONDITION does not work for TRIM() function then it will also not work when used as code.

/* Build up new_condition and emit previous condition */

%if &first %then %do;

%if (&n > 1) %then %put new_condition is case when (&new_condition) ;

%let new_condition=(%sysfunc(trim(&condition)));

%end;

%else %let new_condition=&new_condition or (%sysfunc(trim(&condition))) ;

Re: Macro loop problem

forumsguy — Fri, 19 Jul 2013 10:24:48 GMT

Hi Peter,

I was just curious how to get this code working with a slight modification..

Data _null_ ;

Call execute('case when label1='||label1||' 'and label2='||label2|| 'and label3'='||label3'and''( ' ) ;

Do until( last.label3) ;

Set have;

By label1 label2 label3 ;

Call execute( '( ' !! CONDITION !! ' ) ' ) ;

If not last.label3 then

Call execute( ' or ' );

End ;

Call execute( ' ) ' ) ;

Run;

But its giving errors.. can you please help me out with solution of this??? thanks in advance and helping me to learn new things

Re: Macro loop problem

Peter_C — Fri, 19 Jul 2013 14:28:15 GMT

Why use LABEL1 before the SET statement?

I would suggest placing that definition of the "fixed part" after the BY statement and only when FIRST.LABEL3

Do you need me to write the syntax?

Re: Macro loop problem

forumsguy — Fri, 19 Jul 2013 17:07:52 GMT

Not the complete code.. just call execute first part only which contains the and statement. It would be of great help.. if you guide.. since I am getting different errors every time ...

Re: Macro loop problem

Peter_C — Sat, 20 Jul 2013 18:13:36 GMT

here is how to investigate the problem(s) with the code you posted earlier.

Perhaps the problems all lay in the line.

Call execute('case when label1='||label1||' 'and label2='||label2|| 'and label3'='||label3'and''( ' ) ;

rearrange it with fixed pitch text and white space to align it

It could be rearranged as

Call execute(

'case when label1=' || label1 ||'

'and label2=' || label2 ||

'and label3'='|| label3 'and' '( '

) ;

Now I can see the problem!

The code won’t align properly because the quotes are mis-placed.

I expect you understand the difference between constants which appear within quotes, and the names of variables.

And perhaps you just find it difficult to adapt other peoples’ code. That earlier code of mine was untested and bug-infested 😉

Because there are no comparisons (GE, LT, EQ, etc.) in your code above, the only possible problem caused by adding that “white space” is that you will get more white space on your output. However, it helps to spot where the code has become “unbalanced”.

I consider the layout of code the biggest assistance to de-bugging.

Next time your code gives you a problem, lay it out to match the processing you need – stage by stage. Only once you are satisfied that all the piece you need are in place, will you be ready to collapse white space, functions and statements.

Layout is a big part of how I debug/analyse programs. You may have better ideas.

Once that quoting is properly placed, you’ll find you need to prefix the call execute() output (which goes into the submit stream immediately after this step completes because that is the way call execute() works). I would suggest an asterisk because then the strings generated will appear within a comment.

Much improvement is needed to make the whole thing work. Having gone through a testing exercise, I’m beginning to realise that my (untested) suggestion in the earlier posting was missing a few features, (like:1 generating a closing semicolon, 2: STOPping at the right point).

Rather than populate a macro variable directly through call execute(), I’m beginning to see a better solution might write the logic code to a file. That could then be invoked by %include.

Mostly the change is just a matter of replacing call execute() with PUT statements.

Here is one I prepared with generated sql code in mind

*'; *"; */; * clean up unbalanced quotes;

Data _null_ ;

file 'generated logic code.txt' ;

if enough then DO ;

PUT ' END ; ' ;

STOP ;

END;

* data step iteration for a row ;

if _n_ = 1 then PUT '%LET LOGIC_CODE= CASE ' ;

Do until( last.label3) ;

* do loop iteration for each condition ;

* i.e. each condition within one set of label1-3 ;

Set have end= enough ;

By label1 label2 label3 ;

* on first for a set of label1-3 report those labels ;

If first.label3 then PUT ' WHEN ' label1= '& '

label2= '& '

label3= '& ' '(' ;

* "named" put style with those "=" is really helpful ;

FORMAT LABEL1-LABEL3 $QUOTE200. ;

* the FORMAT statement directs the PUT to quote the values of label1-3 ;

* now for each CONDITION, report it with ( parentheses ) ;

PUT '( ' CONDITION ' ) ' ;

* if not last for a set of label1-3 need to say OR ;

If not last.label3 then PUT ' OR ' ;

End ;

* now after all conditions reported, close with another ) ;

PUT ' ) ' ;

Run;

* now check the generated text ;

PROC FSLIST FILE= 'generated logic code.txt' ;

RUN;