topic Re: Work days between events in SAS Programming

Work days between events

RandyStan — Sat, 30 May 2020 19:43:00 GMT

Dear All:

May data is as follows

ID_A ID_B Date VAR_A

1 100 03FEB2020 -5

1 100 06FEB2020 3

1 100 11FEB2020 0

1 100 14FEB2020 0

1 100 18FEB2020 10

1 100 24FEB2020 10

2 100 28JAN2020 0

2 100 29JAN2020 0

2 100 31JAN2020 -2

2 200 05FEB2020 0

2 200 12FEB2020 5

I want to construct VAR_B which is the Work Days to the next VAR_A = 0

So my data set should look like

ID_A ID_B Date VAR_A VAR_B

1 100 03FEB2020 -5 3

1 100 06FEB2020 3 7

1 100 11FEB2020 0

1 100 14FEB2020 0

1 100 18FEB2020 10 4

1 100 24FEB2020 10 -9999 /*The last observation of each group is -9999 */

2 100 28JAN2020 0

2 100 29JAN2020 0

2 100 31JAN2020 -2 8

2 200 05FEB2020 0

2 200 12FEB2020 -5 -9999 /*The last observation of each group is -9999 */

Thanx in advance

Randy

Re: Work days between events

PGStats — Sat, 30 May 2020 20:33:14 GMT

Please explain VAR_B=4 here, where there is no posterior VAR_A=0 observation in the ID=1 group

ID_A ID_B Date VAR_A VAR_B

1 100 03FEB2020 -5 3

1 100 06FEB2020 3 7

1 100 11FEB2020 0

1 100 14FEB2020 0

1 100 18FEB2020 10 4

1 100 24FEB2020 10

2 100 28JAN2020 0

Re: Work days between events

RandyStan — Sat, 30 May 2020 20:51:18 GMT

Dear PGStats

18FEB2020 to 24FEB2020 is 4 working days -- 19,20,21,24.

Thanks so much

Re: Work days between events

PaigeMiller — Sat, 30 May 2020 21:17:24 GMT

Explain VAR_B=3 on the first row. There are 8 days to the place where VAR_A=0, so even if you exclude Saturdays and Sundays, you don't get 3.

Re: Work days between events

RandyStan — Sat, 30 May 2020 21:28:08 GMT

Dear Mr. Miller:

Let me explain the logic.

03FEB2020 to 06FEB2020 is 3 work days -- hence the 3 (days)

Because VAR_A = 0 on 11FEB2020 and 14FEB2020 the next day to be considered is 18FEB2020 is 7 workdays (17FEB2020) was a holiday.

And so forth.

If you need any other clarification please do not hesitate.

Thanx so much.

Randy

Re: Work days between events

PGStats — Sat, 30 May 2020 21:47:08 GMT

So, your statement should read

VAR_B which is the number of Work Days to the next observation where VAR_A ne 0 ?

Re: Work days between events

RandyStan — Sat, 30 May 2020 22:06:47 GMT

Yes.

Apologies.

Re: Work days between events

PaigeMiller — Sat, 30 May 2020 23:13:38 GMT

@RandyStan, typographical errors aside, your explanation still is unclear.

In row 2, you show VAR_B should be 7, even though there is only 5 days between that line and the next line. Where does that number 7 come from?

Can you please write a complete and clear description so we don't have to scroll up and down and piece together different parts of your explanation into a whole?

Re: Work days between events

PGStats — Sun, 31 May 2020 03:25:45 GMT

This will get the WEEKDAYs between the dates:

data have;
input ID_A ID_B Date :date9. VAR_A;
format date yymmdd10.;
datalines;
1                   100              03FEB2020                        -5
1                   100              06FEB2020                         3
1                   100              11FEB2020                          0
1                    100             14FEB2020                          0
1                    100              18FEB2020                        10
1                    100              24FEB2020                        10
2                    100              28JAN2020                         0
2                    100              29JAN2020                         0
2                    100              31JAN2020                        -2
2                    200              05FEB2020                        0
2                    200              12FEB2020                        5
;

proc sort data=have; by ID_A descending date; run;

data want;
do until(last.ID_A);
    set have; by ID_A;
    if first.id_a then var_b = -9999;
    else do;
        if var_a ne 0 and not missing(start) then 
            var_b = intck("weekday", date, start);
        else call missing(var_b);
        end;
    output;
    if var_a ne 0 then start = date;
    end;
drop start;
run;

proc sort data=want; by id_a date; run;

proc print data=want noobs; run;

ID_A 	ID_B 	Date 	VAR_A 	var_b
1 	100 	2020-02-03 	-5 	3
1 	100 	2020-02-06 	3 	8
1 	100 	2020-02-11 	0 	.
1 	100 	2020-02-14 	0 	.
1 	100 	2020-02-18 	10 	4
1 	100 	2020-02-24 	10 	-9999
2 	100 	2020-01-28 	0 	.
2 	100 	2020-01-29 	0 	.
2 	100 	2020-01-31 	-2 	8
2 	200 	2020-02-05 	0 	.
2 	200 	2020-02-12 	5 	-9999

To get the workdays, excluding holidays, I think you will need to use custom intervals .

Re: Work days between events

RandyStan — Fri, 05 Jun 2020 00:52:44 GMT

Thank you so much