topic how to calculate every 3 date without holidays in SAS Programming

how to calculate every 3 date without holidays

thanikondharish — Sat, 15 Dec 2018 16:53:33 GMT

i	holiday
01/12/2018	.
02/12/2018	02/12/2018
03/12/2018	.
04/12/2018	.
05/12/2018	.
06/12/2018	.
07/12/2018	.
08/12/2018	08/12/2018
09/12/2018	09/12/2018
10/12/2018	.
11/12/2018	.
12/12/2018	.
13/12/2018	.
14/12/2018	.
15/12/2018	15/12/2018
16/12/2018	16/12/2018
17/12/2018	.
18/12/2018	.
19/12/2018	.
20/12/2018	.
21/12/2018	.
22/12/2018	22/12/2018
23/12/2018	23/12/2018
24/12/2018	.
25/12/2018	.
26/12/2018	.
27/12/2018	.
28/12/2018	.
29/12/2018	.
30/12/2018	30/12/2018
31/12/2018	.

I have above dataset I have one question i.e.,

every date must be added 3days continuously dates like (itself date date+2) (03dec2018) so in this case when you are adding if any holidays don't consider those dates like see below dataset

I have tried but but i coudn't . Please find this problem.

i	holiday	a
01/12/2018	.	3
02/12/2018	02/12/2018	5
03/12/2018	.	5
04/12/2018	.	6
05/12/2018	.	7
06/12/2018	.	10
07/12/2018	.	11
08/12/2018	08/12/2018	12
09/12/2018	09/12/2018	12
10/12/2018	.	12
11/12/2018	.	13
12/12/2018	.	14
13/12/2018	.	17
14/12/2018	.	18
15/12/2018	15/12/2018	19
16/12/2018	16/12/2018	19
17/12/2018	.	19
18/12/2018	.	20
19/12/2018	.	21
20/12/2018	.	24
21/12/2018	.	25
22/12/2018	22/12/2018	26
23/12/2018	23/12/2018	26
24/12/2018	.	26
25/12/2018	.	27
26/12/2018	.	28
27/12/2018	.	29
28/12/2018	.	.
29/12/2018	.	.
30/12/2018	30/12/2018	.
31/12/2018	.	.

Re: how to calculate every 3 date without holidays

Reeza — Sat, 15 Dec 2018 20:12:25 GMT

@thanikondharish wrote:

i

holiday

01/12/2018

.

02/12/2018

02/12/2018

03/12/2018

.

04/12/2018

.

05/12/2018

.

06/12/2018

.

07/12/2018

.

08/12/2018

08/12/2018

09/12/2018

09/12/2018

10/12/2018

.

11/12/2018

.

12/12/2018

.

13/12/2018

.

14/12/2018

.

15/12/2018

15/12/2018

16/12/2018

16/12/2018

17/12/2018

.

18/12/2018

.

19/12/2018

.

20/12/2018

.

21/12/2018

.

22/12/2018

22/12/2018

23/12/2018

23/12/2018

24/12/2018

.

25/12/2018

.

26/12/2018

.

27/12/2018

.

28/12/2018

.

29/12/2018

.

30/12/2018

30/12/2018

31/12/2018

.

I have above dataset I have one question i.e.,

every date must be added 3days continuously dates like (itself date date+2) (03dec2018) so in this case when you are adding if any holidays don't consider those dates like see below dataset

I have tried but but i coudn't . Please find this problem.

i

holiday

a

01/12/2018

.

3

02/12/2018

02/12/2018

5

03/12/2018

.

5

04/12/2018

.

6

05/12/2018

.

7

06/12/2018

.

10

07/12/2018

.

11

08/12/2018

08/12/2018

12

09/12/2018

09/12/2018

12

10/12/2018

.

12

11/12/2018

.

13

12/12/2018

.

14

13/12/2018

.

17

14/12/2018

.

18

15/12/2018

15/12/2018

19

16/12/2018

16/12/2018

19

17/12/2018

.

19

18/12/2018

.

20

19/12/2018

.

21

20/12/2018

.

24

21/12/2018

.

25

22/12/2018

22/12/2018

26

23/12/2018

23/12/2018

26

24/12/2018

.

26

25/12/2018

.

27

26/12/2018

.

28

27/12/2018

.

29

28/12/2018

.

.

29/12/2018

.

.

30/12/2018

30/12/2018

.

31/12/2018

.

.

Please post your data as a data step and include your attempts.

Re: how to calculate every 3 date without holidays

mkeintz — Sat, 15 Dec 2018 23:53:52 GMT

What is the rule

Apparently your goal is to generate the A values based on the current date and the status of holiday dates between the current date and the desired future data.

But I can't figure out what rule you have. Every rule I think of has an except in your data. Please explain mathematically.

Re: how to calculate every 3 date without holidays

mkeintz — Sun, 16 Dec 2018 00:45:06 GMT

I think you first record value of A is wrong.

This appears to be the rule:

For any non-holiday, count forward 2 dates, excluding holidays
For any holiday assign the value obtained for the immediately succeeding non-holiday.

If so, then:

data have;
  input date :ddmmyy10.  holiday :ddmmyy10. a;
  format date holiday yymmddn8. ;
datalines;
01/12/2018   .              3 
02/12/2018   02/12/2018     5 
03/12/2018   .              5 
04/12/2018   .              6 
05/12/2018   .              7 
06/12/2018   .             10 
07/12/2018   .             11 
08/12/2018   08/12/2018    12 
09/12/2018   09/12/2018    12 
10/12/2018   .             12 
11/12/2018   .             13 
12/12/2018   .             14 
13/12/2018   .             17 
14/12/2018   .             18 
15/12/2018   15/12/2018    19 
16/12/2018   16/12/2018    19 
17/12/2018   .             19 
18/12/2018   .             20 
19/12/2018   .             21 
20/12/2018   .             24 
21/12/2018   .             25 
22/12/2018   22/12/2018    26 
23/12/2018   23/12/2018    26 
24/12/2018   .             26 
25/12/2018   .             27 
26/12/2018   .             28 
27/12/2018   .             29 
28/12/2018   .              . 
29/12/2018   .              . 
30/12/2018   30/12/2018     . 
31/12/2018   .              . 
run;

data want (drop=_:);
  merge have 
        have (firstobs=3 keep=date holiday rename=date=date3) ;
  where holiday =.;
  day3=day(date3);
  do until (_date=date);
    set have (rename=date=_date);
    output;
  end;
run;

The strategy here is

The merge statement
1. Reads only the non-holidays (see the "where holiday=.;" statement)
2. The merge reads two copies of HAVE. The 2nd copy starts at the 3rd non-holiday record, keeps only 2 variables holiday and date - which is renamed to date3. This give the desired future date.
Get the day-of-month (day3) for date3, which should match variable A (except for the apparent error in the first value of A).
Now, because some holidays may have been skipped, reread ALL records (using _DATE as the incoming record date) until _DATE=DATE . Output each such record, all of which (both holidays and immediately following non-holidays) will have the same DATE3 and DAY3 values.