How to resolve an error related to macro execution?

[Sheeba]

Hi,

Added few details below

I have a ctrl dataset which has the following structure

Ctrl

seq_num     A                    condn                                   B

1      A1                  eq                                           B1

1     A2                   le                                            B2

1     A3                   ge                                           B3

2      A4                  le                                            B4

3     A5                   ge                                           B5

3    A6                   eq                                            B6

I have created a macro createjoin() which sorts this dataset by id creates the following  condition from this data set

CONDITION1 if a1 eq b1 and a2 le b2 and A3 ge b3 ;

CONDITION 2 if a4 le b4;

Condition 3 if A5 ge B5 and a6 eq b6;

The code is as follows

%macro m_createjoins(mv_seq_num);

/*Sort the dataset*/

proc sort data=input.ctrl;

by seq_num;

run;

data input.ctrl_mod;

    set input.ctrl;

where seq_num = mv_seq_num;

/*Create the joining condition, count and store it in macrovariables*/

data _null_;

length jstmt $400;

set input.ctrl_mod; end=eof;

by seq_num;

if first.seq_num then

do;

jstmt=' ';

jstmt=catx(' ','if',A,condition,B);

end;

if first.seq_num = 0 then

jstmt=catx(' ',jstmt,'and (',A,condition,B,')');

if last.seq_num then

do;

jstmt=catx(' ',jstmt,'then','do',';');

put jstmt=;

flag+1;

call symputx('m_join_condition',jstmt,'g');

 end;

retain jstmt;

if eof then

estmt =cats (' ', 'end',';');

call symputx('m_estmt',estmt,'g');

run;

%mend m_createjoins;

I am trying to use this macro in the following merge

data A;

input id rs xxx code ;

datalines;

1 34 0 10

2 34 0 10

3 20 0 30

;

run;

data B;

input id rev code ;

datalines;

1 34 0 20

2 34 0 30

3 20 0 10

;

run;

proc sort data=a;

by code;

run;

proc sort data=b;

by code;

run;

/*here i am trying to pass the value of id to the macro create join and create the conditions for the corresponding id each time i call the macro */

%macro temp;

data C;

merge A B;

by code;

if rs= 34 then

do;

if id =1 then

do;

%m_createjoins(1);

&&m_join_condition

xxx=123;

&m_estmt

end;

end;

run;

%mend temp;

%temp;

I am getting errors probably due to the timing of macro variables resolution. Is there any way i can call the macro from the datastep by passing the parameter , generate only the appropriate condition and get the condition in the do loop?

When i modified the code for the macro temp , it is working fine. Here i executed the macro once for all and created the condition , stored it in the macro variables starting from 1 to n and  substituting the condition.

%macro temp;

data B;

set A;

if rs= 34 then

do;

if id =1 then

do;

&&rm_join_condition1

xxx=123;

&m_estmt

end;

else if id =2 then

do;

&&rm_join_condition2

&m_estmt

end;

end;

run;

%mend temp;

%temp;

 Is there any way i can call the macro to generate only the condition which i require at that point?

Appreciate any help.

Thanks in advance,

Regards,

Sheeba

[ballardw]

SAS doesn't use "and if". if you have multiple comparisons it is a simple and.

if a=b and c>d and e<=f then do ...

[Sheeba]

hi ballardw,

Thanks for the quick reply.

I submitted the edited code. the error is resolved at that point.

But I am getting the following error on the line where macro variables resolves to if condition

Statement is not valid or it is used out of proper order.

Regards,

Sheeba

[Reeza]

Do you conditions have semicolons?

Remember it has to resolve to valid SAS code. 

Use option MPRINT to see what's being generated.

option MPRINT SYMBOLGEN;

[Sheeba]

Hi reeza,

Thanks for the reply.

 The conditions are having semi colons.

here in the approach below i am trying to call the macro for each value of id and create conditions corresponding to the value of id and trying to get the condition. eg when id is one i am creating only condition1 and getting it here. I am getting error here.

%macro temp;

data C;

merge A B;

by code;

if rs= 34 then

do;

if id =1 then

do;

%m_createjoins(1);

&&m_join_condition

xxx=123;

&m_estmt

end;

end;

run;

%mend temp;

%temp;

When i modified the code for the macro temp , it is working fine. Here i executed the macro once for all and created the conditions , stored it in the macro variables starting from 1 to n and  substituting the variables.eg rm_join_condition1 for id 1 rm_join_condition2 for id2 and it is working fine.

%macro temp;

data B;

set A;

if rs= 34 then

do;

if id =1 then

do;

&&rm_join_condition1

xxx=123;

&m_estmt

end;

else if id =2 then

do;

&&rm_join_condition2

&m_estmt

end;

end;

run;

%mend temp;

%temp;

I am assuming it is  due to any problem in getting macro variables values in the first block of code.

regards,

Sheeba

[Reeza]

Post the log from running your code with MPRINT on. 

[Sheeba]

Hi Reeza,

Thanks for the reply.

Below code is working fine ..This is how the code looked like before i attempted to make a macro out of it. But I am not sure if the below approach is the best one.

data _null_;
 length jstmt $400;
set input.ctrl end=eof;
by seq_num;
if first.seq_num then
do;
 jstmt=' ';
 jstmt=catx(' ','if',A,condition,B);
end;
if first.seq_num = 0 then
 jstmt=catx(' ',jstmt,'and (',A,condition,B,')');
if last.seq_num then
do;
 jstmt=catx(' ',jstmt,'then','do',';');
put jstmt=;
 flag+1;
call symputx('m_join_condition',jstmt,'g');
 end;
retain jstmt;
if eof then
 estmt =cats (' ', 'end',';');
 call symputx('m_estmt',estmt,'g');
run;

data A;
input id rs xxx code ;
datalines;
1 34 0 10
2 34 0 10
3 20 0 30
;

run;

/* the below code is working fine but do you think below is the best approach*/

%macro temp;
data B;
  set A;
 if rs= 34 then do;
   if id =1 then do;
     &&rm_join_condition1
         xxx=123;
     &m_estmt
             end;
    else if id =2 then do;
     &&rm_join_condition2
        xxx=234;
     &m_estmt
             end;
     end;
run;
%mend temp;
%temp;

[ballardw · Accepted solution]

If you are continuing to use this bit of code, or something that looks like it you have a serious execution issue.

%macro temp;
data C;
   merge A B;
   by code;
   if rs= 34 then
   do;
     if id =1 then
     do;
       %m_createjoins(1);
       &&m_join_condition
       xxx=123;
       &m_estmt
     end;
  end;
run;
%mend temp;

your macro call %m_createjoins(1) contains a proc sql and data step. As soon as that marco call is hit the data step for Data c terminates because it has hit a boundary - the proc sql call.

In effect the code is

data c;

    merge a b;

...

    do;

    Proc sql .....

which should throw errors.

Can you show what the functioning code before you attempted to make a macro of it looked like?

I'm almost thinking that you may want to try writing statements to a text file before the data step and use %include instead of resolve that many macros inline.

Also this line looks like you are trying to use the set option end. There should not be ; before end if that is the case. Otherwise you are creating a likely missing value for two variables End and Eof.

set input.ctrl_mod; end=eof;

[Sheeba]

Hi Ballardw,

Thanks for the reply.

it was a mistake on my part to put a semi colon before end statement in the code. Thanks for pointing it out.

This is how the code looked like before i put it into a macro.

Ctrl

seq_num     A                    condn                                   B

1     A1                   eq                                           B1
1     A2                    le                                           B2
1     A3                    ge                                           B3
2     A4                   le                                           B4
3     A5                    ge                                           B5
3     A6                    eq                                           B6

I have created a macro createjoin() which sorts this dataset by id creates the following  condition from this data set

CONDITION1 if a1 eq b1 and a2 le b2 and A3 ge b3 ;
CONDITION 2 if a4 le b4;
Condition 3 if A5 ge B5 and a6 eq b6;

Macro Createjoin
proc sort data=input.ctrl;
by seq_num;
run;

data _null_;
 length jstmt $400;
set input.ctrl end=eof;
by seq_num;
if first.seq_num then
do;
 jstmt=' ';
 jstmt=catx(' ','if',A,condition,B);
end;
if first.seq_num = 0 then
 jstmt=catx(' ',jstmt,'and (',A,condition,B,')');
if last.seq_num then
do;
 jstmt=catx(' ',jstmt,'then','do',';');
put jstmt=;
 flag+1;
call symputx('m_join_condition',jstmt,'g');
 end;
retain jstmt;
if eof then
 estmt =cats (' ', 'end',';');
 call symputx('m_estmt',estmt,'g');
run;

data A;
input id rs xxx code ;
datalines;
1 34 0 10
2 34 0 10
3 20 0 30
;

run;

/* the below code is working fine but do you think below is the best approach*/

%macro temp;
data B;
  set A;
 if rs= 34 then do;
   if id =1 then do;
     &&rm_join_condition1
         xxx=123;
     &m_estmt
             end;
    else if id =2 then do;
     &&rm_join_condition2
        xxx=234;
     &m_estmt
             end;
     end;
run;
%mend temp;
%temp;

Regards,

Sheeba