macro nested loop group by

[yashpande]

i All,

I have following dataset which needs to be read using macro and created nested case statements.

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;

my output should be like this

CASE Label1

WHEN A

THEN CASE LABEL2

WHEN D THEN CASE LABEL3

WHEN P THEN CASE CONDITION

WHEN (CODE1 OR CODE7) THEN FLAG="Y"

END

END

WHEN B

THEN CASE LABEL2

WHEN E THEN CASE LABEL3

WHEN Q THEN CASE CONDITION

WHEN (CODE2 OR CODE3) THEN FLAG="Y"

END

END

WHEN C

THEN CASE LABEL2

WHEN P THEN CASE LABEL3

WHEN  THEN CASE CONDITION

WHEN (CODE4) THEN FLAG="Y"

WHEN D

THEN CASE LABEL2

WHEN A THEN CASE LABEL3

WHEN R THEN CASE CONDITION

WHEN (CODE5 OR CODE6) THEN "Y"

else "N"

end as active_flag

I read     This post and tried to modify it but somehow was unable to get the logic clear. I was stuck into this part.

%let first=%eval( &n=1 or %superq(prev_label1) ne %superq(label1)

I know I need nested loop in this only, but somehow was unable to figure it out since i am new to %syscall , open , close functions.

I also read this post.

https://communities.sas.com/thread/33885?start=0&tstart=0

My requirement is exactly same but in Macro language.

Need some guidance to resolve this

[Reeza]

You can't use cards/datalines inside macros.

Is your data specified already? Or are you trying to read a specific dataset using a macro and then process it automatically?

[yashpande]

I am trying to use this have dataset. . Cards is not used in my macro

[Tom]

Can you re-state the problem, because I cannot figure out what you want to do.

Also what does macro have to do with it?  It really does not look like a problem where macro coding is what you want.

Also some example data that the generated case statements are going to operate on.  I cannot figure out if values of LABEL1 ('A', 'D', etc) represent values of some unspecified variable or names of variables.

[yashpande]

Sure Tom. My sample dataset is this

data have;

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

cards;

a d    p code1

a d    p code7

a e    q code2

a e    q code3

b p      code4

b a    r code5

d a    r code6

;

run;

Now I used your code in .

If you look at condition for first, its only one level your are checking if all three variables equal to their previous previous values. I want to check for three levels one below other. like

%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)     

      )

  ;

/* Build up new_condition and emit previous condition */

  %if &first %then %do;

%let j=0;

%let second=%eval( &j=1

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

      )

  ;

  %if &second %then %do;

%let k=0;

%let second=%eval( &k=1

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

      )

  ;

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

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

  %end;

%end;

%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);

Hope i am making sense. Bold part is something where I am confused. How to do nested loop and print something like this

so I need only 2 case statements for this one for Label1=A and one for Label1=B ... Its group by label 1 then label2 and then label3

Hope I am making sense now....

[Reeza]

It would help if you post your sample output instead I think.

[yashpande]

Ya it was seeming difficult to explain... so here we go ....

Input

data new;

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

cards;

a d p code1

a d p code7

a e q code2

a e q code3

b p   code4

b a r code5

d a r code6

;

run;

Output :

proc sql;

create table op as

select label1 , label2 , label3,condition,

case label1

when 'a' then

    case label2

        when 'd' then

        case label3

            when 'p' then 

            case condition

                when 'code1' then 'y'

                when 'code7' then 'y'

                else 'n'

            end

        else 'n'

        end

        when 'e' then case label3

            when 'q' then

            case condition

                when 'code2' then 'y'

                when 'code3' then 'y'

                else 'n'

            end       

        end

        else 'n'

        end       

when 'b' then

    case label2

    when 'a' then 

        case label3

        when 'r' then'y'

        when '' then 'y' else 'n'

        end

    else 'n'

    end

when 'd' then

    case label2

    when 'a' then 

    case label3

    when 'r' then'y'

    else 'n'

    end

    else 'n'

    end

    else 'n'

end as meta_flag

from new

;

quit;

All my case statements has to be generated from Macro.

I hope I am making it clear now... Please need help on solving this

[mkeintz]

Do you have to generate sql code with a "group by" expresssion?  You could ignore the nested structure of the rules, and quite easily produce this code:

proc sql;

   create table op as select label1 , label2 , label3,condition,

   case catx('/',label1,label2,label3,condition)

     when ("a/d/p/code1" ) then 'y'

     when ("a/d/p/code7" ) then 'y'

     when ("a/e/q/code2" ) then 'y'

     when ("a/e/q/code3" ) then 'y'

     when ("b/p/code4" ) then 'y'

     when ("b/a/r/code5" ) then 'y'

     when ("d/a/r/code6" ) then 'y'

     else 'n'

   end as meta_flag

   from new;

quit;

[yashpande]

Hi mkeintz.. hope that was the case. But nope , macro nested loop is the requirement. . Please help me guys...

[mkeintz]

So this is a homework?  Not a job assignment?

[yashpande]

?????? this is job assignment only. Its time difference buddy. it was 11 pm im india. anyways, I have couple more days to finish this..

[yashpande]

Hi Tom ,I was Using your code which you wrote in the other post... it seemed to be most suited for this requirement as well. I am sure that in your first if where you check for all labels with its previous values , we need three nested loops one below other .. some how I am unable to correct it. Please help me in understanding the logic for this...

[Tom]

First try generating the code using PUT statements in a data step using FIRST. and LAST. flags to get the logic down.

If you can do that then you might be able to adjust the macro code by expanding the FIRST macro flag to calculate flags FIRST_LABEL1  , FIRST.LABEL2 etc.

For example to calculate FIRST_LABEL1 you would use (&N=1 or "label1" ne "prev_label1") .

For FIRST_LABLE2 you would use (&first_label1 or "label2" ne "prev_label2")

similarly for label3.

[yashpande]

Trying so very hard but its not working out. I so hate nested loops in macros now. Will try with data steps tomorrow morning n see how it goes. In the mean time , I tried like this but its not working obviously.  Can you see where m going wrong

%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_label1=%eval( &n=1

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

      )

  ;

/* Build up new_condition and emit previous condition */

  %if &first_label1%then %do;

   % let first_label2=% eval

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

);

% let first_label3=% eval

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

);

  %if (&n > 1 and &prev_label1 and &prev_label2 and &prev_label3) %then

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

%put new_condition is case when (&new_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);

Thanks a ton in advance

[Reeza]

yashpande wrote:

it seemed to be most suited for this requirement as well.

I'm confused, as I think others are, as to why this approach which would be used, when it doesn't seem efficient and in fact is quite confusing. 

This "requirement" aspect makes it seem like a homework assignment rather than a work assignment.