topic Re: macro nested loop group by in SAS Programming

macro nested loop group by

yashpande — Wed, 24 Jul 2013 17:17:02 GMT

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

WHEN B

THEN CASE LABEL2

WHEN E THEN CASE LABEL3

WHEN Q THEN CASE CONDITION

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

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

Re: macro nested loop group by

Reeza — Wed, 24 Jul 2013 17:23:58 GMT

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?

Re: macro nested loop group by

yashpande — Wed, 24 Jul 2013 23:22:22 GMT

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

Re: macro nested loop group by

Tom — Thu, 25 Jul 2013 00:08:06 GMT

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.

Re: macro nested loop group by

yashpande — Thu, 25 Jul 2013 03:04:13 GMT

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;

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

Re: macro nested loop group by

Reeza — Thu, 25 Jul 2013 03:38:42 GMT

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

Re: macro nested loop group by

yashpande — Thu, 25 Jul 2013 05:22:17 GMT

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

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

Re: macro nested loop group by

mkeintz — Thu, 25 Jul 2013 12:37:11 GMT

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;

Re: macro nested loop group by

yashpande — Thu, 25 Jul 2013 13:45:20 GMT

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

Re: macro nested loop group by

yashpande — Thu, 25 Jul 2013 14:26:17 GMT

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

Re: macro nested loop group by

Tom — Thu, 25 Jul 2013 14:38:16 GMT

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.

Re: macro nested loop group by

yashpande — Thu, 25 Jul 2013 17:16:39 GMT

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

Re: macro nested loop group by

mkeintz — Thu, 25 Jul 2013 20:06:57 GMT

So this is a homework? Not a job assignment?

Re: macro nested loop group by

yashpande — Thu, 25 Jul 2013 23:35:56 GMT

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

Re: macro nested loop group by

Reeza — Thu, 25 Jul 2013 23:58:22 GMT

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.

Re: macro nested loop group by

yashpande — Fri, 26 Jul 2013 00:48:12 GMT

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

Re: macro nested loop group by

Tom — Fri, 26 Jul 2013 01:16:26 GMT

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

Re: macro nested loop group by

mkeintz — Fri, 26 Jul 2013 03:23:39 GMT

- 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

Re: macro nested loop group by

forumsguy — Mon, 29 Jul 2013 11:59:35 GMT

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;

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

/* 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;

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

/* 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;

%end;

/* close the meta rules table */

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

%put ELSE "N" end as Meta1;

%mend rules;

%rules(have);