BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yashpande
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
forumsguy
Fluorite | Level 6

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

18 REPLIES 18
Reeza
Super User

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
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User

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

yashpande
Obsidian | Level 7

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
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
yashpande
Obsidian | Level 7

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

mkeintz
PROC Star

So this is a homework?  Not a job assignment?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
yashpande
Obsidian | Level 7

?????? 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
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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