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:
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.
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 ;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.