Variable values not changing during data step execution and call execute of macro

[Gayle]

Hi I have written som code to go through a table of tablenames and take each tablename and put it in to a sql statement in a macro.  There are 2 table names in the table the first table name is read in and put into the WHERE clause and according to debug the value does change to the 2nd table name but the macro always keeps the first table name.   Can you help me figure out why?

Here is the data step:

data _null_/debug;
	set dbtblnames_complete;
	%put  &newtblname1;
	%let  crnttblname = &newtblname1;
	%put &crnttblname;
	call execute('%nrstr(%%tstuniqvalid(&newtblname1));');
run;

Here is the macro that gets called:

%macro tstuniqvalid(crnttblname);
proc sql;

connect to teradata as eiwp (username=&xxxuser. password=&xxxpass. tdpid=&xxxx_TDPID. mode=teradata fastload=yes);
create table unique_validcustid as
select count(*) from connection to eiwp (
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
);
%put "SQL Return code and message" &sqlxrc &SQLXMSG;
%mend;

According to the debug the variable newtblname1  is getting changed 

> go
newtblname1 = dbname.table1
Old value =
Value changed at line 8964 column 5
Stepped to line 8968 column 5
> go
newtblname1 = dbname.table2
Old value = dbname.table1
Value changed at line 8964 column 5
> go
newtblname1 =
Old value = dbname.table2
Value changed at line 8964 column 5
> go

but each iteration has the first table name (dbname.table1) only.

I can't figure out why. Please take a look and advise. Thank you

[Tom]

You seem confused about the order of execution of macro code, data steps and code pushed to be executed by CALL EXECUTE.  Also since this data step is not doing anything with any data why would you want to try to use the data step debugger with it?

Since the data step is not doing anything with the data from dbtblnames_complete the only impact that dataset will have how many times that the data step will iterate. So if it has 3 observations then your first data step is essentially running this.

%put &newtblname1;
%let crnttblname = &newtblname1;
%put &crnttblname;

data _null_;
  call execute('%nrstr(%%tstuniqvalid(&newtblname1));');
  call execute('%nrstr(%%tstuniqvalid(&newtblname1));');
  call execute('%nrstr(%%tstuniqvalid(&newtblname1));');
run;

Which will result in these three statements being submitted to run after the DATA _NULL_ step finishes.

%%tstuniqvalid(&newtblname1);
%%tstuniqvalid(&newtblname1);
%%tstuniqvalid(&newtblname1);

Not sure what the impact is of the extra % character there.

[CarmineVerrell]

As mentioned in a previous response, the order of execution is very important to learn. If you have already taken a programming 2 class, i would recommend you take a Macro 1 class too. In the 2-day Macro 1 class, you will learn in detail how macro code gets execute and why the code you are submitting is not the correct way to submit macros. That way you can debug and correct these errors yourself in the future.

[Gayle]

Thanks i will take those courses, right now I am wrapping up the getting started programming course for sas

[Gayle]

Thanks you I am new to SAS programming

[Shmuel · Accepted solution]

%LET is not a data step statement. 

Assuming crnttblname is the variable in dbtblnames_complete data set

concatenate it in the call execute function:

data _null_/debug;
 set dbtblnames_complete;
     call execute('%nrstr(%%tstuniqvalid(' || strip(crnttblname) || '));');
run;

 Try even:

call execute('%tstuniqvalid(' || strip(crnttblname) || ');');

[Gayle]

Thank you! concatenating it worked: call execute('%tstuniqvalid(' || strip(crnttblname) || ');');.  I do not really understand why this works yet.

[Shmuel]

Call Execute argument is a string to submit.

Suppose cmttblname = "Table1" then the submitted string is

%tstuniqvalid(Table1);

[Gayle]

Yes I understand but it only started working when I put the STRIP and concatenated the '. Oh so you mean the parameter has to be in quotes when i do the call execute.

[Kurt_Bremser]

This:

data _null_/debug;
	set dbtblnames_complete;
	%put  &newtblname1;
	%let  crnttblname = &newtblname1;
	%put &crnttblname;
	call execute('%nrstr(%%tstuniqvalid(&newtblname1));');
run;

is equivalent to this:

%put  &newtblname1;
%let  crnttblname = &newtblname1;
%put &crnttblname;

data _null_/debug;
	set dbtblnames_complete;
	call execute('%nrstr(%%tstuniqvalid(&newtblname1));');
run;

as the macro statements are resolved while the code is being fetched for the data step compiler.

Since &newtblname1 never changes, all your macro calls will receive the same parameter.

[Gayle]

thanks , still a bit fuzzy though

[Tom]

It looks like you are trying to run a query in Teradata for each table/dataset name that you have in some dataset?

You seem to be generating a single number from this query?

Is the goal to start with a dataset like this:

data have ;
  input tablename :$32. ;
cards;
table1
table2
table3
;

And generate a table like this?

data want;
  input tablename :$32.  count ;
cards;
table1 10
table2 0
table3 23456
;