I need to calculate a process date from an add date.
for each record I have a add_date to reflect the day it was added
I need to calculate a batch date which is 1 day after the add_date except on Weekends.
Tuesday batches add_dates for Sunday and Monday
my code below works except for the monday batch
data test_dates;
set trials;
cal_date = datepart (add_date);
batch_date=intnx('day',cal_date,1);
format batch_date yymmddp10.;
run;
How do I get Sunday add_dates to have batch_dates = tuesday?
Hi:
I feel more information is needed. What happens if the ADD_DATE is on Friday -- do you want the BATCH_DATE to fall on Saturday (which is part of the weekend)? Or what about if the ADD_DATE is Saturday? What should the BATCH_DATE be? Monday or Tuesday?? Is there something about your data where the add_date will NEVER fall on Friday or Saturday???
cynthia
Friday add_date = monday batch_date
Saturday add_date = tuesday batch_date
Sunday add_date = tuesday batch date
Monday add_date = tuesday batch_date
tuesday add_date = wednesday batch_date
maybe this will clarify a little better that above
data is added everyday except on thanksgiving (US holiday)
however the data only batched Monday threw Friday for the previous days work
Hi:
I am a visual kind of person. So this is what I derived from your explanation using some fake days:
id fake data has add_date: falls on: Datepart returns: BATCH_DATE wanted: Logic:
--- ------------------- ----------- ----------------- ------------------ ---------
A 23AUG2010:12:14:21 (Monday=2) August 23, 2010 2010.08.24 advance add_date by 1 day
B 24AUG2010:13:24:22 (Tuesday=3) August 24, 2010 2010.08.25 advance add_date by 1 day
C 25AUG2010:14:34:23 (Wednesday=4) August 25, 2010 2010.08.26 advance add_date by 1 day
D 26AUG2010:15:44:24 (Thursday=5) August 26, 2010 2010.08.27 advance add_date by 1 day
E 27AUG2010:16:54:25 (Friday=6) August 27, 2010 (Monday) 2010.08.30 advance add_date by 3 days
F 28AUG2010:18:04:26 (Saturday=7) August 28, 2010 (Tuesday) 2010.08.31 advance add_date by 3 days
G 29AUG2010:18:04:27 (Sunday=1) August 29, 2010 (Tuesday) 2010.08.31 advance add_date by 2 days
H 30AUG2010:19:14:28 (Monday=2) August 30, 2010 (Tuesday) 2010.08.31 advance add_date by 1 day
so...this is what I think you want to do. Note that for my own clarification, I added the "falls on" column of info so I could figure out what day of the week my fake dates fell on so I could, in turn, figure out what the BATCH_DATE should be set to. I see two ways to do this:
1) test the day of the week for CAL_DATE (which you can get from SAS by using the WEEKDAY function on your CAL_DATE variable and then change the INTNX interval accordingly (using IF statements)
OR
2) INTNX allows you to shift the time interval and even specify a custom shift interval -- you can find examples of this in the doc and in user group papers
#1 is probably the quickest method and #2 could be fast or could be time-consuming to develop, especially if you have to use a custom shift interval, depending on your comfort level with using INTNX and customizing the shift interval, programming.
cynthia
You can use the WEEKDAY function to find the day of the week. Then you can add extra days for Fri, Sat and Sun.
data xx ;
do add_date = today() to today()+6;
add_day=weekday(add_date);
batch_date = add_date + 1 + (add_day in (6 7 1)) + (add_day in (6 7)) ;
batch_day=weekday(batch_date);
put (add_day add_date batch_day batch_date) (=);
end;
format add_date batch_date weekdate3. ;
run;
add_day=7 add_date=Sat batch_day=3 batch_date=Tue
add_day=1 add_date=Sun batch_day=3 batch_date=Tue
add_day=2 add_date=Mon batch_day=3 batch_date=Tue
add_day=3 add_date=Tue batch_day=4 batch_date=Wed
add_day=4 add_date=Wed batch_day=5 batch_date=Thu
add_day=5 add_date=Thu batch_day=6 batch_date=Fri
add_day=6 add_date=Fri batch_day=2 batch_date=Mon
It can be done in one statement. Edit the little pipe-separated string if necessary to get the correct offsets.
data _null_ ;
do cal_date = today() to today()+6 ;
batch_date =
intnx( 'day'
, cal_date
, input( scan( '2|1|1|1|1|3|2'
, weekday(cal_date)
, '|' )
, f. ) ) ;
put (cal_date batch_date)(= downame.) ;
end ;
run ;
Result:
cal_date=Sunday batch_date=Tuesday
cal_date=Monday batch_date=Tuesday
cal_date=Tuesday batch_date=Wednesday
cal_date=Wednesday batch_date=Thursday
cal_date=Thursday batch_date=Friday
cal_date=Friday batch_date=Monday
cal_date=Saturday batch_date=Monday
mick_g wrote:
I need to calculate a process date from an add date.
for each record I have a add_date to reflect the day it was added
I need to calculate a batch date which is 1 day after the add_date except on Weekends.
Tuesday batches add_dates for Sunday and Monday
my code below works except for the monday batch
data test_dates;
set trials;
cal_date = datepart (add_date);
batch_date=intnx('day',cal_date,1);
format batch_date yymmddp10.;
run;
How do I get Sunday add_dates to have batch_dates = tuesday?
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.