topic date calculation in SAS Programming

date calculation

mick_g — Fri, 01 Jul 2011 17:16:21 GMT

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?

date calculation

Cynthia_sas — Fri, 01 Jul 2011 18:07:30 GMT

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

date calculation

mick_g — Fri, 01 Jul 2011 18:16:49 GMT

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

date calculation

mick_g — Fri, 01 Jul 2011 18:19:03 GMT

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

Re: date calculation

Cynthia_sas — Fri, 01 Jul 2011 19:40:16 GMT

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)

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

Re: date calculation

Howles — Mon, 04 Jul 2011 01:25:54 GMT

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?

Re: date calculation

Tom — Sat, 09 Jul 2011 16:25:57 GMT

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