Data step question

[Gayle]

from my understanding the data step is like a loop.  I have the code below and dbtblnames_complete has 2 entries I am getting a different dbms error for each. However the %put 

statement at the end of the macro(for &sqlxrc &SQLXMSG) only shows the second error 2 times.   What is going on?  Where am 

I misunderstanding?

%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 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
);
%put "SQL Return code and message" &sqlxrc &SQLXMSG;
quit;
%mend;


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

so

[ChrisNZ]

I am unsure what the problem is, but:

1. from connection to xxxx     should be    from connection to EIWP

2. you create the same data set  unique_validcustid  for each observation of   dbtblnames_complete so the data set will get overwritten

[Gayle]

1. yes that is what it is in my code .the question/problem is : this code will do validation and in the data step there is a table of 2 table names being read want to capture the error code for each of the 2 tables. The first table does not have one of the column names and the second one the user does not have select access. However the "put" statement only gives me the error code/msg for the second table 2 times.
like so:
SQLXRC=3523
SQLXMSG=The user does not have SELECT access to WORK_05.ST_xxxx_chd_LOAD
SQLXRC=3523
SQLXMSG=The user does not have SELECT access to WORK_05.ST_xxxx_chd_LOAD

[Astounding]

As you might have guessed, you are missing a key concept here.  CALL EXECUTE is not so intuitive when macro language is involved.  It runs code as soon as possible.  That means macro language statements execute immediately, but DATA and PROC step code have to wait until the DATA step is over.  As a result, the steps that take place (in order are):

1. The DATA step begins to execute
2. CALL EXECUTE runs your macro the first time.  It has to wait to run, PROC SQL, but ...
3. CALL EXECUTE runs the %PUT statement immediately.  The DATA step has not finished, nor has PROC SQL run yet.
4. CALL EXECUTE runs your macro the second time.  Again, it has to wait to run PROC SQL, but ...
5. CALL EXECUTE runs the %PUT statement (for the second time) immediately.
6. The DATA step ends.
7. The PROC SQLs run.

The usual fix is to prevent CALL EXECUTE from running macro code immediately is to enclose it in the %NRSTR function.  You may need to check the syntax on this if this doesn't do it:

	call execute('%nrstr("%tstuniqvalid(" || strip(newtblname1) || ");"');

[Gayle · Accepted solution]

thanks so much this worked (i.e call execute('%nrstr("%tstuniqvalid(" || strip(newtblname1) || ");"');, !!I will have to read more about this key concept to understand the flow

[Tom]

Classic timing issue with CALL EXECUTE() and macros.

Your macro has a mix of macro code and actual SAS code.  If you let SAS run the macro while it is pushing the call onto the stack to run after your data step then the macro statements run before the SAS code the macro generates has a chance to run.

Do not use data step debugger with this. It is not adding any value.

data _null_;
  set dbtblnames_complete;
  call execute(cats('%nrstr(%tstuniqvalid)(' ,newtblname1, ');'));
run;

You will notice the difference immediately in the SAS log.  Instead of seeing the SAS code the macro generates with the + in the LOG you will see just the macro call in the log.

1252  %macro test(name);
1253  proc print data=&name (obs=1); run;
1254  %mend test;
1255
1256  data _null_;
1257    do name='sashelp.class';
1258      call execute(cats('%test(',name,')'));
1259    end;
1260  run;

NOTE: DATA statement used (Total process time):
      real time           0.13 seconds
      cpu time            0.01 seconds


NOTE: CALL EXECUTE generated line.
1    + proc print data=sashelp.class (obs=1); run;

NOTE: There were 1 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.09 seconds
      cpu time            0.01 seconds


1261
1262  data _null_;
1263    do name='sashelp.class';
1264      call execute(cats('%nrstr(%test)(',name,')'));
1265    end;
1266  run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
1    + %test(sashelp.class)

NOTE: There were 1 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

[PhilC]

Hi all, related question for my own knowledge.  Is there a downside in using DoSubl() for the OP's code? My naive opinion is that it would fix the timing.

[ChrisNZ]

It would.
Call dosubl() starts a new SAS session that runs in parallel and executes immediately.
The cost of doing this is high though.

[Gayle]

Thanks so much for explaining further and I took out the debug