topic Re: How to - Trigger a BAT File After a Job Finish by SAS Code? in SAS Programming

How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Tue, 23 Jan 2018 14:22:03 GMT

Hello everyone,

I have a data set as below and it feeds from IT department.

If whole FINAL_DATE rows include “date of the day”(for example -> 23JAN2018) and whole ERROR rows are equal to zero then it should trigger a .bat file.

To do this, I need IF-ELSE control structures and it should control every half an hour.

After it started the bat file, to prevent to execute the code until the next day, I need an another IF-ELSE control structure.

At this point, I need your help. Can someone help me about this, please?

Data Have;
Length FINAL_DATE 8 ERROR 8;
Infile Datalines Missover;
Input FINAL_DATE ERROR ;
FORMAT FINAL_DATE  DATE9.;
Datalines;
21207 0
21207 0
21207 0
21207 0
21207 0
21207 0
21207 0
21207 0
21207 0
21207 0
21207 0
;
Run;

To Trigger a BAT file, I have this;

Data _Null_;
X "......\Batch.bat";
RUN;

Thank you,

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Tue, 23 Jan 2018 15:20:05 GMT

This looks like a task for the original scheduler that your IT uses. They should simply run the batch when all your jobs have their condition OK. A very simple task for them, using the proper tools for the job.

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Tue, 23 Jan 2018 15:22:47 GMT

Firstly, thank you for your time.

I'm agree with you but what if I have to do this in SAS environment. Aren't there any ways to do in SAS?

Thank you,

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Tue, 23 Jan 2018 16:12:48 GMT

I suggest an indefinitely looping macro that checks, and then either sleeps for 30 minutes or executes the shell script/bat and sleeps until next day:

%macro main_loop;
%do %until 0;

  proc sql noprint;
  select max(error) into :errcheck from have where final_date = "....";
  quit;

  %if &errcheck
  %then %do;

    data _null_;
    x = sleep(1800,1);
    run;

  %end;
  %else %do;

    data _null_;
    call system("....bat");
    now = datetime();
    tomorrow = intnx('dtday',now,1,'b');
    sleeptime = tomporrow - now;
    x = sleep(sleeptime,1);
    run;

  %end;

%end;
%mend;

%main_loop

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Tue, 23 Jan 2018 16:38:25 GMT

Thank you for your time again but it gives errors as below, I do not understand the reason.

ERROR: Expected parenthesis not found after UNTIL.

ERROR: A dummy macro will be compiled.

180

WARNING: Apparent invocation of macro MAIN_LOOP not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Tue, 23 Jan 2018 17:00:53 GMT

Change the %do statement:

%do %until (0);

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Tue, 23 Jan 2018 17:54:00 GMT

in conclusion, What do we provide by writing %DO %UNTIL (0) ; statement? Can you explain me, shortly?

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Quentin — Tue, 23 Jan 2018 18:19:22 GMT

As Kurt mentioned, %do %until(0) will create an infinite loop. That is, the loop will keep iterating for ever, because 0 is false. So %until condition will never become true, and the loop will keep iterating for ever.

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Tue, 23 Jan 2018 18:49:00 GMT

@turcay wrote:

in conclusion, What do we provide by writing %DO %UNTIL (0) ; statement? Can you explain me, shortly?

The logical value "true" is represented by a numeric value 1, and "false" is 0. You could use

%do %while (1);

for the same purpose.

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Thu, 25 Jan 2018 07:35:09 GMT

Hello again,

Thank you for your explanation. It is more clear now.

I have one more question now. The table HAVE is fed as DATETIME, I mean the variable FINAL_DATE has a format DATETIME21. So I put the following code into your macro.

  DATA WANT;
     SET HAVE;
     FINAL_DATE=DATEPART(FINAL_DATE);
     FORMAT FINAL_DATE DATE9.;
     RUN;

Then it was like this ->;

%MACRO main_loop;
%do %until (0);
 
     DATA WANT;
     SET HAVE;
     FINAL_DATE=DATEPART(FINAL_DATE);
     FORMAT FINAL_DATE DATE9.;
     RUN;
 
  proc sql noprint;
  select max(ERROR) into :errcheck from WANT where FINAL_DATE = "&SYSDATE9."D;
  quit;
 
  %if &errcheck
  %then %do;
 
    data _null_;
   x = sleep(1800,1);/*Wait until 1800 seconds*/
    run;
 
  %end;
  %else %do;
 
    data _null_;
    call system("...\Batch_Test.bat");/*EXECUTE THE BAT FILE*/
    now = datetime();/*Give that date and time*/
    tomorrow = intnx('dtday',now,1,'b');/*'b'-> beginning*//*Next day*/
    sleeptime = tomorrow - now;
    x = sleep(sleeptime,1);
    run;
 
  %end;
 
%end;
%MEND;
 
%main_loop

I wonder is it stilll work after my addtional step into macro?

In addition, I execute this code by calling new BAT file. IT can execute the code every 30 minutes by the help of Windows Scheduler. Can that make our macro code easier?

Thank you,

Can Yılmazer

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Wed, 24 Jan 2018 17:06:58 GMT

Of course, running the code regularly off the scheduler will make it easier, as the indefinite loop is not needed. You might want to work with manually setting the return code, so that the scheduler can decide if the code needs to be rerun for a given date.

Your code addition looks fine to me.

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Thu, 25 Jan 2018 07:39:14 GMT

Hello Kurt,

Thank you for your time again. So the following code and Scheduler(Every half an hour) will be enough for this process? On the other hand, what if somebody open the WANT data set, while the code was working in background. There can be lock error? Any method to prevent this?

%MACRO main_loop;

DATA WANT;
     SET HAVE;
     FINAL_DATE=DATEPART(FINAL_DATE);
     FORMAT FINAL_DATE DATE9.;
     RUN;
 
  proc sql noprint;
  select max(ERROR) into :errcheck from WANT where FINAL_DATE = "&SYSDATE9."D;
  quit;
 
  %if &errcheck
  %then %do;
 
    data _null_;
   x = sleep(1800,1);/*Wait until 1800 seconds*/
    run;
 
  %end;
  %else %do;
 
    data _null_;
    call system("C:\Users\sas.c.yilmazer\Documents\BATCHTEST\Batch_Test.bat");/*EXECUTE THE BAT FILE*/
    now = datetime();/*Give that date and time*/
    tomorrow = intnx('dtday',now,1,'b');/*'b'-> beginning*//*Next day*/
    sleeptime = tomorrow - now;
    x = sleep(sleeptime,1);
    run;
 
  %end;

%MEND;
 
%main_loop

Thank you,

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Thu, 25 Jan 2018 08:18:46 GMT

It's very hard (basically impossible) for somebody to open your want dataset, as it's in the WORK directory of the SAS process. WORK directories are readable only by the owning user (or the superuser, but that's a given), and they have the process number coded in their name.

In order to inspect people's WORK data, I have to log in as superuser, find the directory according to owner/process number, and copy the .sas7bdat in question to a location where I can pick it up with my personal workspace server session. That's a highly administrative task.

Since your code will now be triggered by the scheduler, the sleep() functions are not necessary anymore (there's also no looping).

Instead you should set a specific exit code that tells the scheduler how to proceed further. You can do this with the abort statement in a data _null_ step.

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Thu, 25 Jan 2018 08:58:31 GMT

Thank you for detail explanation again,

I realized something so I have few more questions to you.

Firstly, if Final_Date rows can be different with each other, &errcheck still returns zero, shouldn't it return 1? Because every Final_Date rows and every ERROR rows should be same to trigger the code?

Here is a sample which returns 0 even Final_Date rows are different;

Data Want;
Length FINAL_DATE 8 ERROR 8;
Infile Datalines Missover;
Input FINAL_DATE ERROR ;
FORMAT FINAL_DATE  DATE9.;
Datalines;
21208 0
21208 0
21208 0
21209 0
21209 0
21209 0
21209 0
21209 0
21209 0
21209 0
21209 0
;
Run;
PROC SQL NOPRINT;
  SELECT MAX(ERROR) INTO :errcheck FROM WANT WHERE FINAL_DATE = "&SYSDATE9."D;
QUIT;
  %PUT &errcheck.;

For my second problem, let's pretend we solved the foregoing Final_Date problem. If the code is executed in that today, how can I tell the code, it should wait until nextday &errcheck variable will be 0? Can you help about it?

By the way, I put the Abort variable as below;

%MACRO main_loop;
 
PROC SQL NOPRINT;
  SELECT MAX(ERROR) INTO :errcheck FROM WANT WHERE FINAL_DATE = "&SYSDATE9."D;
QUIT;
 
  %IF &errcheck
  %THEN %DO;

  DATA _NULL_;
  ABORT CANCEL;
  RUN;

  %END;
  %ELSE %DO;
 
    data _null_;
    call system("...\Batch_Test.bat");/*EXECUTE THE BAT FILE*/
    run;
 
  %END;

%MEND;
 
%main_loop

Thank you

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Thu, 25 Jan 2018 09:13:52 GMT

We're tinkering around with code without really knowing the issue at hand.

Please define (clearly)

- which data you receive, and when

- which action needs to be taken, under which conditions present in the data

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Thu, 25 Jan 2018 10:29:19 GMT

Okay, sorry for missunderstanding,

The Have 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.

When Final_Date and Error rows will be same then it should trigger the bat file.(This represents that the data set are ready to work for that day)

After bat file was executed, it should wait the next day until Final_Date and Error rows should be same for next day.

For example;

The following table will work for today, but, because scheduler works for every half an hour. I need to prevent to execute the code if it worked for that day.

The code should wait until the table will be as below;

I hope I could tell you better?

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Thu, 25 Jan 2018 11:07:06 GMT

Hello again,

I updated the code, &errrcheck works which we expected, I just need to tell the code this -> "Wait or do not execute, if the code worked once for that day". How can I tell it?

%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 WANT);
QUIT;

%IF &errcheck %THEN %DO;

DATA _NULL_;
ABORT CANCEL;
RUN;

%END;
%ELSE %DO;/*I need to put a control here*/

DATA _NULL_;
CALL SYSTEM("...\Batch_Test.bat");/*EXECUTE THE BAT FILE*/
RUN;

%END;
%MEND;
%MAIN_LOOP

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Thu, 25 Jan 2018 14:41:26 GMT

If you hand the repeating part over to the scheduler, you don't need a macro at all:

proc sql noprint;
select count(distinct final_date), max(final_date), max(error) into :datecount,:max_date,:errcheck
from want;
quit;

data _null_;
if &datecount = 1 and &max_date = today() and &errcheck = 0
/* only one date that is today, and no errors */
then call system("...\Batch_Test.bat");
else abort return 5;
run;

The scheduler just needs to run the program every 30 minutes as long as it ends with a return code of 5.

As soon as it ends with 0, execution can be suspended until the next day.

If any other return code (1 = WARNING, 2 = ERROR) happens, the scheduler should trigger the usual alert.

Edit: removed a %put that was solely for debugging.

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

turcay — Thu, 25 Jan 2018 15:30:29 GMT

Hello Kurt,

Your code seems what I want, I am really much appreciated thank you.

I also added a code as below, will it work, what do you think?

%MACRO MAIN_LOOP;

DATA WANT;
     SET HAVE;
     FINAL_DATE=DATEPART(FINAL_DATE);
     FORMAT FINAL_DATE DATE9.;
     RUN;

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 WANT);
QUIT;

%IF &errcheck %THEN %DO;

DATA _NULL_;
ABORT CANCEL;
RUN;

%END;
%ELSE %DO;

DATA _NULL_;
     SET PERMANENT_LIBRARY.WANT(OBS=1);
     IF FNL_FINAL_DATE=FINAL_DATE THEN DO; ABORT CANCEL; END;
     ELSE DO;
    call system("...\Batch_Test.bat");
     END;
RUN;

 DATA PERMANENT_LIBRARY.WANT;
     SET WANT(OBS=1);
	 FNL_FINAL_DATE=FINAL_DATE;
	 FORMAT FNL_FINAL_DATE DATE9.
 RUN;
 

%END;
%MEND;
%MAIN_LOOP

Re: How to - Trigger a BAT File After a Job Finish by SAS Code?

Kurt_Bremser — Thu, 25 Jan 2018 16:21:06 GMT

Try it.