Hello everyone,
I try to automatize the whole process in customer environment so the code should be scheduled and works a batch job. The following BATCH data set is updated everyday, but it is unclear when the data set will be updated so we need to check every half an hour. For this reason, I prepared the following code and scheduled by the help of WindowsTask scheduler for every half an hour.
Data BATCH;
Length FINAL_DATE 8 ERROR 8;
Infile Datalines Missover;
Input FINAL_DATE ERROR ;
FORMAT FINAL_DATE DATE9.;
Datalines;
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
;
LIBNAME PORT "C:\Users\...\Desktop";
Data PORT.STATUS;
Start_Date="Y";
Complete_Date="01MAR2018"D;
Format Complete_Date DATE9.;
Run;
Options dbidirectexec bufno=100 bufsize=128k;
LIBNAME SQL ODBC DATAsrc=proddb SCHEMA=sas USER=user PASSWORD="*******" INSERT_SQL=YES INSERTBUFF=32767 readbuff=10000 bulkload=yes dbcommit=10000;
%MACRO main_loop;
PROC SQL;
SELECT MAX(RESULT) INTO:errcheck FROM(SELECT *,
CASE WHEN FINAL_DATE="&SYSDATE9."D AND ERROR=0 THEN 0 ELSE 1 END AS RESULT
FROM BATCH);
QUIT;
%IF &errcheck %THEN %DO;
DATA _NULL_;
ABORT CANCEL;
RUN;
%END;
%ELSE %DO;
DATA _NULL_;
SET PORT.STATUS;
IF Start_Date="Y" OR Complete_Date="&sysdate9."d THEN ABORT CANCEL;
ELSE Call system("...Batch.bat");
RUN;
%END;
%MEND;
%main_loop
When the above code executes the bat file the following code is triggered but I need to update PORT.STATUS data set because the code run time can take an hour so when the Windows Task Scheduler trigger the code it shouldn't re-execute the following code if it is already running.
The problem is if I execute the following code on Enterprise Guide, it works without an error but when it is triggered by Batch job it gives an LOCK error for PORT.STATUS. Actually, both Batch and EG session running on the same exact environment and I am trying to access the exact same data set and I am also pretty sure the PORT.STATUS data set does not open anywhere beacuse I'm the only who knows the PORT.STATUS data set.
LIBNAME PORT "C:\Users\...\Desktop";/**/
DATA PORT.STATUS;/*It gives the LOCK error here*/
SET PORT.STATUS;
Start_Date="Y";
Run;
/*MAIN CODE IS HERE BUT I DO NOT TYPE IT BECAUSE IT CAN TAKE UP A LOT OF SPACE*/
DATA PORT.STATUS;
SET PORT.STATUS;
Complete_Date="&sysdate9."d;
format Complete_Date date9.;
Start_Date="N";
Run;
Firstly, do you have an idea what can be the reason for?
Secondly, do you have an alternative solution to prevent the lock error?
Thank you,
... View more
