how to return a value from a macro called from a data step

[Gayle]

I am doing a call execute from a data set to execute a macro and return the gsqlrc(which contains sqlxrc) and gsqlmsg(which contains sqlxmsg).  However, the 2 assignment statements are getting a null value. I look at the value in the macro and it is not null.  Any ideas?

data final_validation;
	set dbtblnames_complete;
	tablename=newtblname1;
	call execute('%nrstr(%deterifcolsexist(&var1,&var2,' || strip(newtblname1) || '));');
	call execute('%nrstr(%tstuniqvalid(' || strip(newtblname1) || '))');
	sqlerrcode=symget('gsqlrc');
	sqlerrmsg=symget('gsqlmsg');

[PeterClemmensen]

Call Execute stacks code for execution after the Data Step terminates. 

You probably need the Dosubl Function.

[Reeza · Accepted solution]

CALL EXECUTE only executes at the end of a data step, so you cannot use the values returned within the data step. You could replace it with DOSUBL which will execute immediately.
Or revisit your program and see if a function via PROC FCMP would be more appropriate than a macro.

[Gayle]

Thank you .  I did use DOSUBL but i get ERROR 180-322: Statement is not valid or it is used out of proper order. do you see the error?  I have not figured it out.

data final_validation;
	set dbtblnames_complete;
	tablename=newtblname1;
	call execute('%nrstr(%deterifcolsexist(&var1,&var2,' || strip(newtblname1) || '));');
	 rc = dosubl('%nrstr(%tstuniqvalid(' || strip(newtblname1) || '))');
	/*%put &gsqlrc &gsqlmsg;*/;
	sqlerrcode=symget('gsqlrc');
	sqlerrmsg=symget('gsqlmsg');

[Reeza]

My guess is your other CALL EXECUTE also needs to be a DOSUBL. Does anything happen there that's required for the macro after?

[Gayle]

no nothing that is there in that call is required for the macro

[Gayle]

well it looks like it did not like the nrstr. I removed that however I still am not getting the expected return values . I get nulls(.)

[Reeza]

Time to show the rest of your code or a reproducible example.

[Gayle]

ok here is what I am trying to accomplish: 

This macro gets called: 

%macro tstuniqvalid(crnttblname);
proc sql;
connect to teradata as xxxx (username=&xxuser. password=&xxpass. tdpid=&xxWP_TDPID. mode=teradata fastload=yes);
create table unique_validcustid as
select * from connection to xxxx (
select  hist_ky_add
		,count(*)
from (select cast(cust_id as decimal(15,0)) as cust_id_test
	,cust_id 
	,cust_id_type 
	,cell_ky
	,rank(cust_id || cust_id_type || cast(cell_ky as varchar(16))asc)as hist_ky_add
from &crnttblname) a
group by 1
having count(*) > 1
);

%global gsqlrc gsqlmsg;

%if &sqlxrc ne 0 %then
   %do;
	   %put "Test load table for uniqueness and valid cust_id FAILED due to SQL error";
	   %let gsqlrc=&sqlxrc;
	   /*%put &=gsqlrc;*/
	   %let gsqlmsg=&sqlxmsg;
	   /*%put &=gsqlmsg;*/
	   data _null_;
        call symput('gsqlrc',gsqlrc);
        call symput('gsqlmsg',gsqlmsg);
	    %put "in the data step of tstuniqu";
	    %put &=gsqlrc "rc in the data step";
	    %put &=gsqlmsg "msg in the data step";
	   run;
   %end;

and what i am trying to do in it is return the sql return code and message when it is not 0 (i e there is a sql error) . So that i can put it in a table.It is called from here:  The symgets are where I am trying to assign the returned values to a table(final_validation);

data final_validation;
	set dbtblnames_complete;
	tablename=newtblname1;
	call execute('%nrstr(%tstuniqvalid(' || strip(newtblname1) || '))');
       rc = dosubl('%tstuniqvalid(' || strip(newtblname1) || ')');
	sqlerrcode=symget('gsqlrc');        
	sqlerrmsg=symget('gsqlmsg');
	put "Put data in the table";
	put sqlerrcode;
	put sqlerrmsg;  

[Reeza]

Why do you have both a call execute and a DOSUBL?
Leave in only the DOSUBL.

I'm assuming you also just missed the %MEND end for your macro?

[Gayle]

hi thank you the %MEND is there just did not put it in the post.  I will make the other DOSUBL but I would not think one impacts the other, they are separate ......

[Gayle]

Thank you Reeza I will use DOSUBL instead of call execute

[Tom]

Passing values through macro variables is probable not what you want to do.  Just have your macro insert observations into a table.  You can then use the values from that table after all of the macro calls have finished.

%macro tstuniqvalid(crnttblname);
proc sql;
connect using xxxx ;
create table unique_validcustid as
select * from connection to xxxx (
select  hist_ky_add
		,count(*)
from (select cast(cust_id as decimal(15,0)) as cust_id_test
	,cust_id 
	,cust_id_type 
	,cell_ky
	,rank(cust_id || cust_id_type || cast(cell_ky as varchar(16))asc)as hist_ky_add
from &crnttblname) a
group by 1
having count(*) > 1
);

insert into final_validation(crnttblname,nobs,sqlxrc,sqlxmsg)
  values ( "&crnttblname",&sqlobs,&sqlxrc,"%superq(sqlxmsg)" )
;
quit;
%mend;


* Connect to teradata ;
libname xxxx teradata username=&xxuser. password=&xxpass. tdpid=&xxWP_TDPID. mode=teradata fastload=yes;

* Create empty summary table ;
data final_validation;
  length crnttblname $128 nobs 8 sqlxrc 8 sqlxmsg $256 ;
  stop;
run;

* Call macro once for each table ;
data _null_;
  set dbtblnames_complete;
  call execute(cats('%nrstr(%tstuniqvalid)(',newtblname1,')'));
run;

You can now just print the dataset if you want to see any tables that caused errors.

proc print data=final_validation;
  where sqlxrc ne 0;
run;

[Kurt_Bremser]

---

@Gayle wrote:
well it looks like it did not like the nrstr. I removed that however I still am not getting the expected return values . I get nulls(.)

---

Yes, tested it, DOSUBL does not like the %NRSTR. %NRSTR is also only needed in CALL EXECUTE to prevent premature execution of macro code.

[Kurt_Bremser]

Start with something simple, and expand from there:

%macro test1(param);
%global &param.;
%let &param. = YYY;
%mend;

data _null_;
rc = dosubl('%test1(XXX)');
xxx = symget('XXX');
put xxx=;
run;

You will see that the DOSUBL method works in creating the macro variables, so that it can be returned by SYMGET.