DATA Step, Macro, Functions and more

macro nested loop group by

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

macro nested loop group by

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


Accepted Solutions
Solution
‎07-29-2013 07:59 AM
Frequent Contributor
Posts: 85

Re: macro nested loop group by

Posted in reply to yashpande

Try this : Not optimized though.. but should work....

data have;

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

datalines;

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;

%macro rules (meta_rule_table_name);

/* open the meta rules table*/

%let meta_rule_table_id=%sysfunc(open(&meta_rule_table_name,i));

/* fetch the first record */

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

%if &rc ne 0 %then

%do;

    %put something is wrong ...;

%end;

%else

%do;

/* read label1, label2, label3 and condition variables

for the very first record */

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

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

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

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

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

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

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

    %sysfunc(varnum(&meta_rule_table_id,CONDITION))));

   

   

/* store the current values for comparision with the next record's values*/

    %let prev_label1 = &label1;

   

    %let prev_label2 = &label2;

    %let prev_label3 = &label3;

    %let reset = 1;

    %if &reset eq 1 %then

    %do;       

         %put case  when  label1 eq "&label1" then case  when  label2 eq "&label2"  then case  when label3 eq "&label3" then case WHEN (&condition);

        %let reset = 0;       

    %end;

   

/* fetch the next record of the meta rules table */

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

   

/* as long as there is a record in the rules table keep looping */

    %do %while (&rc eq 0);

   

/* read the variables for the fetched record */

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

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

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

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

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

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

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

        %sysfunc(varnum(&meta_rule_table_id,CONDITION))));

/* if the current label1 differs from the previous label1 then all three

if conditions need to change */

        %if &label1 ne &prev_label1 %then

        %do;

            %let reset = 1;           

             %put then "Y";

             %put ELSE "N" end;

             %put ELSE "N" end;

            %put ELSE "N" end;           

            %if &reset eq 1 %then

            %do;           

                  %put when label1 eq "&label1" then case  when label2 eq "&label2"  then case  when  label3 eq "&label3" then case WHEN (&condition);

                %let reset = 0;               

            %end;

        %end;

       

/* if the current label2 differs from the previous label2

then only two if conditions need to change */

       

        %else %if &label2 ne &prev_label2 %then

        %do;

            %let reset = 1;                       

             %put then "Y";

             %put ELSE "N" end;

             %put ELSE "N" end;

            %if &reset eq 1 %then

            %do;

                 %let reset = 0;           

                  %put when label2 eq "&label2"  then case  when  label3 eq "&label3" then case when (&condition)    ;

            %end;

        %end;

/* if the current label3 differs from the previous label3

then only one if condition needs to change */

        %else %if "&label3" ne "&prev_label3" %then

        %do;

            %let reset = 1;           

             %put then "Y";

             %put ELSE "N" end;

            %if &reset eq 1 %then            

                %let reset = 0;                   

                  %put when label3 eq "&label3" then case WHEN (&condition);

        %end;

               

/* if label1, label2, label3 are same for both current and

previous records in the  table then only print the subsetting IF statement */

        %else

        %do;

            %if &reset eq 1 %then

            %do;

                 %put (&condition)    ;           

            %end;

            %else

            %do;                                   

                %put or(&condition)  ;                            

            %end;           

            %let reset=0;                   

        %end;

       

       

/* store the current values for comparision with the next record's values before the next fetch */

        %let prev_label1 = &label1;       

        %let prev_label2 = &label2;

        %let prev_label3 = &label3;

/* fetch the next record of the meta rules table */   

   

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

    %end; /* end of %do %while ... */   

     %put then "Y";

    %put ELSE "N" end;

    %put ELSE "N" end;   

    %put ELSE "N" end;           

%end;

/* close the meta rules table */

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

%put ELSE "N" end as Meta1;

%mend rules;

%rules(have);

View solution in original post


All Replies
Super User
Posts: 19,855

Re: macro nested loop group by

Posted in reply to yashpande

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?

Contributor
Posts: 44

Re: macro nested loop group by

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

Super User
Super User
Posts: 7,074

Re: macro nested loop group by

Posted in reply to yashpande

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.

Contributor
Posts: 44

Re: macro nested loop group by

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....

Super User
Posts: 19,855

Re: macro nested loop group by

Posted in reply to yashpande

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

Contributor
Posts: 44

Re: macro nested loop group by

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

Trusted Advisor
Posts: 1,022

Re: macro nested loop group by

Posted in reply to yashpande

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;

Contributor
Posts: 44

Re: macro nested loop group by

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

Trusted Advisor
Posts: 1,022

Re: macro nested loop group by

Posted in reply to yashpande

So this is a homework?  Not a job assignment?

Contributor
Posts: 44

Re: macro nested loop group by

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

Contributor
Posts: 44

Re: macro nested loop group by

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...

Super User
Super User
Posts: 7,074

Re: macro nested loop group by

Posted in reply to yashpande

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.

Contributor
Posts: 44

Re: macro nested loop group by

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

Super User
Posts: 19,855

Re: macro nested loop group by

Posted in reply to yashpande

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 18 replies
  • 792 views
  • 1 like
  • 5 in conversation