BookmarkSubscribeRSS Feed
turcay
Lapis Lazuli | Level 10

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,

 

19 REPLIES 19
Kurt_Bremser
Super User

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.

turcay
Lapis Lazuli | Level 10

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,

Kurt_Bremser
Super User

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  
turcay
Lapis Lazuli | Level 10

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

turcay
Lapis Lazuli | Level 10

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

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Kurt_Bremser
Super User

@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.

turcay
Lapis Lazuli | Level 10

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

 

Kurt_Bremser
Super User

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.

turcay
Lapis Lazuli | Level 10

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,

Kurt_Bremser
Super User

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.

turcay
Lapis Lazuli | Level 10

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

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1453 views
  • 2 likes
  • 3 in conversation