macro nested loop group by

[yashpande]

sir.. you are taking it wrong and catching me literally in words. its language. . I am not too comfortable in expressing.. Anyways...background is i work for  infosys but am fresher...there used to be production code which did this flagging bit using base sas code. recently my client purchased DI studio and teradata. hence they want all this to be converted in  macro code which will be called in autoexec and there will only be 1 mapping in my DI job for flag column instea

[Tom]

I know nothing about DI STUDIO but I really do not see the need for purely function style macro to convert this dataset into code.  You mentioned an autoexec file then why not put whatever code you want in the autoexec to either A) generate a macro variable that you can reference at the appropriate place or B) generate dynamically the code you want as a simple macro definition.

Also I do not understand the need to generated nested case statements.  Why not use each line of the data to generate a single WHEN() clause of one CASE statement. So if your data was just these two lines :

a d p code1

a d p code7

Then you would want to generate a case statement in this form.

case when (label1 = 'a' and label2 = 'd' and label3='p' and condition='code1') then 'y'

        when (label1 = 'a' and label2 = 'd' and label3='p' and condition='code7') then 'y'

        else 'n' end as meta_flag

[mkeintz]

- On DI studio, please see http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0808A&L=sas-l&P=R456&D=0&H=0&O=T&T=1&m=268812

And also Paul Dorfmans' excellent reflections of DI Studio on SAS-L at http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1109b&L=sas-l&F=&S=&P=8121

But back to your question.  Below is some code that might give you a fighting chance.  It reads the specified table and writes the SQL code to the log.  Shouldn't be too hard to convert that to macro construction:

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;

data _null_;
   set have end=end_of_have;
   by label1 label2 label3 condition notsorted;
   L1=quote(trim(label1));
   L2=quote(trim(label2));
   L3=quote(trim(label3));
   Lc=quote(trim(condition));

   if _n_=1 then put 'proc sql;'
       / 'create table op as select label1, label2, label3, condition,'
       / 'case label1';
   if      first.label1 then put
       @3 'when ' L1 'then case label2'
          / @5 'when ' L2 'then case label3'
             / @7 'when ' L3 'then case condition';
   else if first.label2 then put
            @5 'when ' L2 'then case label3'
            / @7 'when ' L3 'then case condition';
   else if first.label3 then put
              @7 'when ' L3 'then case condition';
   put           @9 'when ' LC 'then "y"';
   if last.label3 then put
              @7 'else "n" end';
   if last.label2 then put
            @5 'else "n" end';
   if last.label1 then put
          @3 'else "n" end';
   if end_of_have then put 'else "n" end;' / 'quit;';
run;

Message was edited by: Mark Keintz  - 2nd try at pasting script properly

[forumsguy · Accepted solution]

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