calling macro

[rohithverma]

data rr;
set sashelp.class;
run;
proc sql;
select count(age) into: cnt from rr;
quit;

%macro uu(i,ds);
data g_&i;
set &ds;
run;
proc sql;
create table mismatch as select * from &ds where age not in (select age from g_&i);
%mend;
%macro ff;
%if &cnt> 0 %then %uu(1,sashelp.class) and %uu(2,mismatch);
%mend;
%ff

While i am trying to run the following code it was generating as error in the log 

MLOGIC(UU): Ending execution.
MPRINT(FF): and
MLOGIC(UU): Beginning execution.
2 The SAS System 15:44 Saturday, February 29, 2020

MLOGIC(UU): Parameter I has value 2
MLOGIC(UU): Parameter DS has value mismatch
180: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 180-322: Statement is not valid or it is used out of proper order.
MPRINT(UU): data g_2;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "DS".
34 mismatch
________
22
202
MPRINT(UU): set mismatch;
ERROR 22-322: Syntax error, expecting one of the following: MODE, TRANSACTION.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
MPRINT(UU): run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

MPRINT(UU): proc sql;
MPRINT(UU): create table mismatch as select * from mismatch where age not in (select age from g_2);
ERROR: File WORK.G_2.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MLOGIC(UU): Ending execution.
MLOGIC(FF): Ending execution.
35
36
37 GOPTIONS NOACCESSIBLE;
38 %LET _CLIENTTASKLABEL=;
39 %LET _CLIENTPROJECTPATH=;
40 %LET _CLIENTPROJECTNAME=;
41 %LET _SASPROGRAMFILE=;
42

[PaigeMiller]

When you post a log here, it is important to click on the {i} icon and then paste the log into the window that appears. This preserves the formatting of the log, so that it appears here in the SAS Communities exactly as you see it in SAS. and makes it easier to understand. Please provide the log in that format.

[ed_sas_member]

Hi @rohithverma 

It seems to be a syntax error here:

%if &cnt> 0 %then %uu(1,sashelp.class) and %uu(2,mismatch);

-> I suggest to put:

%if &cnt> 0 %then %do;
	%uu(1, sashelp.class);
	%uu(2, mismatch);
%end;

However, what the macro is trying to achieve is not clear to me:

- the query "select * from &ds where age not in (select age from g_&i);" will never retrieve any value as g_&I is an exact copy of &ds, so mismatch will be empty.

- what is the rational to use mismatch as a parameter in macro %uu?

Could you please explain further what you are trying to do?

Thank you in advance.

Best,

[rohithverma]

oK, Let me explain.

I have created a macro where the output of first execution will be an input for  next execution to the same macro.

Please find the code in below.

%macro utr(i,bnk_inp);

/****************Cond2*************************/
%if &ii>0 and &UTRNAM =0 and &bennam > 0 %then %do;

DATA rr.UTR_&i(drop=BENE_ACK rename=(BENE_ACK_=BENE_ACK));
SET rr.utr_&i;
%if %substr(BENE_ACK,1,2)="00" %then BENE_ACK_=%substr(BENE_ACK,3);
%else BENE_ACK_=BENE_ACK;
run;

proc sql;
create table rr.first_&i as
select *,(a.narration=b."MBOL REFERENCE NO"n) as indic ,
(a.narration ~= "")as step1,
(b."MBOL REFERENCE NO"n ne "") as utr,
coalesce( a.narration ,b."MBOL REFERENCE NO"n) as fid
from rr.&bnk_inp a full join rr.UTR_&i b on compress(a.narration)=compress(b."MBOL REFERENCE NO"n);
quit;

data rr.ref_match_&i;
set rr.first_&i;
where ((indic =1 and step1=1 ) or ( indic=1 and step1=0 and utr=0 and fid= " "));
run;

proc sql;
create table rr.narration_ref_mismatch_&i as
select * from rr.&bnk_inp where compress(narration) not in ( select narration from rr.ref_match_&i );
quit;

proc sql;
create table rr.sec_c2_&i as
select *,(a.narration=b.bene_ack) as indic ,
(a.narration ne "")as step1,
(b.bene_ack ne "") as utr,
coalesce( a.narration ,b.bene_ack) as fid
from rr.narration_ref_mismatch_&i a full join rr.utr_&i b on a.narration=b.bene_ack;
quit;

data rr.ben_match_&i;
set rr.sec_c2_&i;
where (indic =1 and step1=1);
run;

proc sql;
create table rr.overall_mismatch_&i as
select *,"mismatch" as flag from rr.narration_ref_mismatch_&i where compress(narration) not in ( select narration from rr.ben_match_&i );
quit;
%end;
/***********end of cond2*****************/


/***cond3******/

%else %if &UTRNAM > 0 and &bennam =0 %then %do;

DATA rr.UTR_&i(drop="UTR NUMBER"n rename=("UTR NUMBER_"n="UTR NUMBER"n));
SET rr.utr_&i;
if substr("UTR NUMBER"n,1,2)="00" then "UTR NUMBER_"n=substr("UTR NUMBER"n,3);
else "UTR NUMBER_"n="UTR NUMBER"n;
run;

proc sql;
create table rr.first as
select *,(a.narration=b."MBOL REFERENCE NO"n) as indic ,
(a.narration ~= "")as step1,
(b."MBOL REFERENCE NO"n ne "") as utr,
coalesce( a.narration ,b."MBOL REFERENCE NO"n) as fid
from rr.step1_ a full join rr.utr_&i b on compress(a.narration)=compress(b."MBOL REFERENCE NO"n);
quit;

data rr.ref_match;
set rr.first;
where ((indic =1 and step1=1 ) or ( indic=1 and step1=0 and utr=0 and fid= " "));
run;
proc sql;
create table rr.narration_ref_mismatch as
select * from step1_ where compress(narration) not in ( select narration from rr.ref_match );
run;

PROC SQL;
CREATE TABLE rr.sec_c3 AS
select *,(a.narration=b."UTR NUMBER"n) as indic ,
(a.narration ~= "")as step1,
(b."UTR NUMBER"n ne "") as utr,
coalesce( a.narration ,b."UTR NUMBER"n) as fid
from rr.narration_ref_mismatch a full join rr.utr_&i b on compress(a.narration)=compress(b."UTR NUMBER"n);
quit;
data rr.utr_match;
set rr.sec_c3;
where (indic =1 and step1=1);
run;

proc sql;
create table rr.overall_mismatch as
select * from rr.narration_ref_mismatch where compress(narration) not in ( select narration from rr.utr_match );
run;

data rr.final;
set rr.ref_match rr.utr_match rr.overall_mismatch;
run;
%END;

/*****cond4*********/

%else %if &UTRNAM > 0 and &bennam > 0 %then %do;

DATA rr.UTR_&i(drop="UTR NUMBER"n BENE_ACK rename=("UTR NUMBER_"n="UTR NUMBER"n BENE_ACK_=BENE_ACK));
SET rr.utr_&i;
if substr("UTR NUMBER"n,1,2)="00" then "UTR NUMBER_"n=substr("UTR NUMBER"n,3);
else "UTR NUMBER_"n="UTR NUMBER"n;
if substr(BENE_ACK,1,2)="00" then BENE_ACK_=substr(BENE_ACK,3);
else BENE_ACK_=BENE_ACK;
run;

proc sql;
create table rr.first as
select *,(a.narration=b."MBOL REFERENCE NO"n) as indic ,
(a.narration ~= "")as step1,
(b."MBOL REFERENCE NO"n ne "") as utr,
coalesce( a.narration ,b."MBOL REFERENCE NO"n) as fid
from rr.step1_ a full join rr.utr_&i b on compress(a.narration)=compress(b."MBOL REFERENCE NO"n);
quit;

data rr.ref_match;
set rr.first;
where ((indic =1 and step1=1 ) or ( indic=1 and step1=0 and utr=0 and fid= " "));
run;
proc sql;
create table rr.narration_ref_mismatch as
select * from rr.step1_ where compress(narration) not in ( select narration from rr.ref_match );
run;

PROC SQL;
CREATE TABLE rr.sec_c4 AS
select *,(a.narration=b."UTR NUMBER"n) as indic ,
(a.narration ~= "")as step1,
(b."UTR NUMBER"n ne "") as utr,
coalesce( a.narration ,b."UTR NUMBER"n) as fid
from rr.narration_ref_mismatch a full join rr.utr_&i b on compress(a.narration)=compress(b."UTR NUMBER"n);
quit;
data rr.utr_match;
set rr.sec_c4;
where (indic =1 and step1=1);
run;

proc sql;
create table rr.UTR_ref_mismatch as
select * from rr.narration_ref_mismatch where compress(narration) not in ( select narration from rr.utr_match );
run;

proc sql;
create table rr.third_c4 as
select *,(a.narration=b.bene_ack) as indic ,
(a.narration ne "")as step1,
(b.bene_ack ne "") as utr,
coalesce( a.narration ,b.bene_ack) as fid
from rr.UTR_ref_mismatch a full join rr.utr_&i b on a.narration=b.bene_ack;
quit;

data rr.ben_match;
set rr.third_c4;
where (indic =1 and step1=1);
run;

proc sql;
create table rr.overall_mismatch as
select *,"mismatch" as flag from rr.UTR_ref_mismatch where compress(narration) not in ( select narration from rr.ben_match );
run;

data rr.final;
set rr.ref_match rr.utr_match rr.ben_match rr.overall_mismatch;
run;
%end;
%mend ;

%macro uu;
%IF &II > 0 and &FF>0 %THEN %DO;
%UTR(1,step1_);

%UTR(2,overall_mismatch_1);
%END;
%MEND;
%UU

but it still throws errors , even though i am getting correct output .Please look into the issue once

[Tom]

I have created a macro where the output of first execution will be an input for  next execution to the same macro.

If you want to use the macro in that way then write the macro in a way that makes that easier.  For example you could pass in the names of the input and the output datasets.

%macro mymac(inds,outds,....)
...
%mend mymac;

Then you can call the macro twice. It will be up the caller to know that they want to pass in the same name they used as the output of the first call as the input on the second call.

%mymac(step1,step2)
%mymac(step2,step3)

[Patrick]

I strongly recommend that you first write the logic for a specific case not using macro language at all and only once that works you make things dynamic using macro language.

[Tom]

This does not make any sense:

%macro ff;
  %if &cnt> 0 %then %uu(1,sashelp.class) and %uu(2,mismatch);
%mend;

That would only make sense the macro %UU() only returned words that could be used to build a boolean expression (and then you would need to call %FF in a place where it would make sense to have a boolean expression.

Since %UU generates multiple statement, actually multiple steps, of SAS code you need use %DO/%END blocl.

Like this:

%macro ff;
  %if &cnt> 0 %then %do;
    %uu(1,sashelp.class);
    %uu(2,mismatch);
  %end;
%mend;