topic how to return a value from a macro called from a data step in SAS Programming

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

Gayle — Tue, 06 Apr 2021 19:16:52 GMT

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');

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

PeterClemmensen — Tue, 06 Apr 2021 19:18:57 GMT

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

You probably need the Dosubl Function.

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

Reeza — Tue, 06 Apr 2021 19:19:07 GMT

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.

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

Tom — Tue, 06 Apr 2021 19:19:17 GMT

There is no way for the macro to return anything to the current data step since the macro will not even start running until after the data step finishes running.

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

ballardw — Tue, 06 Apr 2021 21:13:55 GMT

Just what are those macros supposed to accomplish?

Without having the definition of the macros it may be that you also have a scope issue of expecting a macro variable to be available outside of the macro using/creating it but is only a local macro variable.

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

Gayle — Tue, 06 Apr 2021 19:59:25 GMT

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');

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

Reeza — Tue, 06 Apr 2021 20:02:37 GMT

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

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

Gayle — Wed, 07 Apr 2021 00:13:18 GMT

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

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

Gayle — Wed, 07 Apr 2021 00:26:32 GMT

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(.)

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

Reeza — Wed, 07 Apr 2021 02:36:37 GMT

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

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

Gayle — Wed, 07 Apr 2021 13:47:08 GMT

I made the variable global

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

Kurt_Bremser — Wed, 07 Apr 2021 13:56:36 GMT

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.

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

Kurt_Bremser — Wed, 07 Apr 2021 14:18:09 GMT

@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.

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

Gayle — Wed, 07 Apr 2021 14:24:23 GMT

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;

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

Reeza — Wed, 07 Apr 2021 14:35:40 GMT

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?

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

Gayle — Wed, 07 Apr 2021 15:16:42 GMT

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 ......

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

Gayle — Wed, 07 Apr 2021 18:02:33 GMT

Thank you Reeza I will use DOSUBL instead of call execute

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

Gayle — Wed, 07 Apr 2021 18:07:54 GMT

thank you! I pared it down and now I seem to be getting what I expect. I had symput in the macro and I guess I was unclear on how it works , removing it helped

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

Tom — Wed, 07 Apr 2021 18:22:30 GMT

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;