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
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 .
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
Dear PGStats
18FEB2020 to 24FEB2020 is 4 working days -- 19,20,21,24.
Thanks so much
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.
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
So, your statement should read
VAR_B which is the number of Work Days to the next observation where VAR_A ne 0 ?
Yes.
Apologies.
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 .
@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?
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.