topic Re: Getting error in sas di after extract transformation run with where filter and same in table loa in SAS Data Management

Getting error in sas di after extract transformation run with where filter and same in table loader

u59166072 — Fri, 18 Jul 2025 17:20:57 GMT

Getting error in sas di after extract transformation run with where filter and same in table loader also same. This is autogenerated code in di. plese find the logs and help me anything to do without manual.

Re: Getting error in sas di after extract transformation run with where filter and same in table loa

u59166072 — Fri, 18 Jul 2025 17:22:02 GMT

Some times run these smoothly without error and some times getting error and stopping the execution.

Re: Getting error in sas di after extract transformation run with where filter and same in table loa

Tom — Fri, 18 Jul 2025 19:24:56 GMT

Not sure why this code would ever work. Perhaps you sometimes do not go down that pathway in your macro logic?

MPRINT(ETLS_CONDITIONW4C43YH):   PROC SQL NOPRINT;
MPRINT(ETLS_CONDITIONW4C43YH):   SELECT COUNT(*) into :REPL_TBL_COUNT TRIMMED from ODS_LAND.ODS_TFAT_RECON_REPL (FIRSTOBS=1 OBS=1);
 
ORACLE_16: Prepared: on connection 1
SELECT * FROM LANDING.ODS_TFAT_RECON_REPL FETCH FIRST                    1 ROWS ONLY
 
SAS_SQL:  Cannot handle dataset options. 
SAS_SQL:  Unable to convert the query to a DBMS specific SQL statement due to an error. 
ACCESS ENGINE:  SQL statement was not passed to the DBMS, SAS will do the processing. 
 
ORACLE_17: Prepared: on connection 1
SELECT  "ADDR_B2KID" FROM LANDING.ODS_TFAT_RECON_REPL  FETCH FIRST                    1 ROWS ONLY

The error message is clear, you cannot use those dataset options with a libref that is pointing to some external database. SAS/Access does not know how to convert them into the SQL dialect that foreign database is using.

Why are you using PROC SQL for this anyway?

SELECT COUNT(*)
  into :REPL_TBL_COUNT TRIMMED 
  from ODS_LAND.ODS_TFAT_RECON_REPL (FIRSTOBS=1 OBS=1)
;

Why not just use a normal DATA STEP ? Since the only values you could get from a single observation would be zero or 1 you could just run this code instead.

%let REPL_TBL_COUNT =0;
data _null_;
  set ODS_LAND.ODS_TFAT_RECON_REPL;
  call symputx('REPL_TBL_COUNT','1');
  stop;
run;

If it was DI that generated that goofy SQL query for you then open a ticket with SAS Support to see if they can fix it.

Re: Getting error in sas di after extract transformation run with where filter and same in table loa

LinusH — Tue, 12 Aug 2025 10:42:09 GMT

It is hard to trouble shoot DI jobs without access to the DI job itself.

But it seems you haven't gone through the whole log yourself?

If a job work sometimes, consider it not to function. You might have a libnme assignment too late in the code, and the second time it works, but not the first.

But, check all the errors in the log and try to fix them one by one.

ERROR: Libref RPTKDC19 is not assigned.
ERROR: SQL View WORK.W1MN90FN could not be processed because at least one of the data sets, or views, referenced directly (or 
       indirectly) by it could not be located, or opened successfully.

How is this libname (not) assigned, perhaps it's defined as pre-assigned by external configuration, and now this is not done?

LIBNAME report ORACLE  PATH=REPOR  SCHEMA=tbaadm  AUTHDOMAIN="tes" ;   %rcSet(&syslibrc);    proc
                                                                                                _____
                                                                                                1
61                                                         The SAS System                                13:09 Friday, July 18, 2025

3278     !  sql NOPRINT;    select count(1) ods_land.ERROR_TEST;    %rcSet(&sqlrc);   run;
ERROR: Specified AuthenticationDomain not found in foundation repository.

Misspelling? Or are you running the job in another environment where "tes" authdomain doesn't exist?