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
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);
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?
I am trying to use this have dataset. . Cards is not used in my macro
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.
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;
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....
It would help if you post your sample output instead I think.
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
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;
Hi mkeintz.. hope that was the case. But nope , macro nested loop is the requirement. . Please help me guys...
So this is a homework? Not a job assignment?
?????? this is job assignment only. Its time difference buddy. it was 11 pm im india. anyways, I have couple more days to finish this..
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...
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: