SAS report of jobs that did not run, department schedule as compared to Oracle batch report EG 7.1

[Timg]

My department runs hundreds of SAS jobs every day that identify groups of people in need of clinical outreach (e.g., people at risk of "X" disease). The identified people are inserted into an Oracle server and we receive a batch confirmation with the Job ID, Creation date, and any errors in return. We would like to develop a process that identifies jobs that were scheduled but did not run. Reasons could include an error code in the batch or lack of a batch entry for that day. The output would be a SAS table with jobs that contain errors and jobs that did not run and have no batch entry.

Proposed Approach:

I think the ideal approach for this report would be to create an Excel file with the scheduled dates of each job (e.g., every day, or Third Friday of Every Month). I see the SAS function "NWKDOM" can handle this type of schedule date data but I am not sure this is the best approach. The Excel file would be brought into SAS as "Table A" and then compared against the batch report "Table B". The NULL values, or missing jobs, would form the report contents. My main challenge is how to represent the jobs schedule in "Table A" e.g., Third Friday of Every Month. Any thoughts on the process or links to similar projects would be appreciated.

Table A, Job Schedule

Scheduled Run Date	Batch ID
Third Friday of Every Month "NWKDOM" function	Batch_1
Every Day "NWKDOM" function	Batch_2
Second Tuesday of Every Month "NWKDOM" function	Batch_3
First Day of Every Quarter "NWKDOM" function	Batch_4

Table B, Today's Batch Jobs

Run Date	Batch ID	Errors
Today's Date	Batch_1	0
Today's Date	Batch_2	1
Today's Date	Batch_3	0
Today's Date	Batch_4	0

Major Steps:

1. Excel file with job schedule dates (e.g., Third Friday of Every Month) and Batch ID
2. SAS creates table of every job that runs on today's date (Run Date = sas_date) from the excel file (Table A)
3. Batch jobs run and are brought into SAS (Table B)
4. SAS process to identify missing jobs from the day's batch. SAS left join B on A. Scheduled Run Date = B.Rundate and A. Batch ID = B. Batch ID
5. Create extract of missing jobs. WHERE Batch ID IS NULL

[blueroad]

you are asking a way to identify 28 dates every year

2nd Tue, 3rd Fri * 12mo = 24

1st day of qtr * 4quarter =   4

I can make a trigger table containing the 28 dates you need for the next 77 years. This will work until 2099.  No functions needed - just start counting.

I'll play with it & post later.

[blueroad]

the below code creates ten years worth of trigger dates in a SAS bdat.  Read that into Excel to control your work.

Prof Bob

data dval ( keep = dt take ) ;
dt = '30Apr2023'd ;  format dt date9. ;
do k = 1 to 3660 ; * about ten years ;
dt = dt + 1 ; take = 0 ;
if ( day(dt) EQ 1 )  then
  if ( month(dt) IN(1,4,7,10) )  then take = 1 ; ;
if ( ( day(dt) IN(8,9,10,11,12,13,14) ) AND 
     ( weekday(dt) EQ 3 ) )      then take = 3 ;  
if ( ( day(dt) IN(15,16,17,18,19,20,21)) AND 
     ( weekday(dt) EQ 6 ) )      then take = 6 ;       
if ( take )  then output ;     
end ;  * k loop ;
run ;