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,
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.
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,
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
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
Change the %do statement:
%do %until (0);
in conclusion, What do we provide by writing %DO %UNTIL (0) ; statement? Can you explain me, shortly?
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.
@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.
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
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.
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,
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.