DATA Step, Macro, Functions and more

date calculation

Reply
Contributor
Posts: 37

date calculation

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?

SAS Super FREQ
Posts: 8,864

date calculation

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

Contributor
Posts: 37

date calculation

Posted in reply to Cynthia_sas

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

Contributor
Posts: 37

date calculation

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

SAS Super FREQ
Posts: 8,864

Re: date calculation

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

Super User
Super User
Posts: 7,042

Re: date calculation

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

Regular Contributor
Posts: 184

Re: date calculation

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?

Ask a Question
Discussion stats
  • 6 replies
  • 240 views
  • 0 likes
  • 4 in conversation