topic Re: Data step question in SAS Programming

Data step question

Gayle — Fri, 05 Feb 2021 02:30:47 GMT

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;

Re: Data step question

ChrisNZ — Fri, 05 Feb 2021 04:22:32 GMT

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

Re: Data step question

Gayle — Fri, 05 Feb 2021 14:52:50 GMT

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

Re: Data step question

Astounding — Fri, 05 Feb 2021 17:34:28 GMT

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):

The DATA step begins to execute
CALL EXECUTE runs your macro the first time. It has to wait to run, PROC SQL, but ...
CALL EXECUTE runs the %PUT statement immediately. The DATA step has not finished, nor has PROC SQL run yet.
CALL EXECUTE runs your macro the second time. Again, it has to wait to run PROC SQL, but ...
CALL EXECUTE runs the %PUT statement (for the second time) immediately.
The DATA step ends.
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) || ");"');

Re: Data step question

Tom — Fri, 05 Feb 2021 17:48:46 GMT

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

Re: Data step question

PhilC — Fri, 05 Feb 2021 20:51:03 GMT

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.

Re: Data step question

ChrisNZ — Sat, 06 Feb 2021 07:30:44 GMT

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

Re: Data step question

Gayle — Mon, 08 Feb 2021 15:07:01 GMT

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

Re: Data step question

Gayle — Mon, 08 Feb 2021 15:09:14 GMT

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