Hi all,
I want to exclude Holidays in given holidays list
For example my data set is like 1 lakh rows are there in this list, one date variable is there
i want exclude holidays from holidays_list(holidays dataset)
formula is
date1= date-1jan2009 (i want count with excluding holidays, Holidays are there in holidays list)
Kindly give solutions
Thanks in advance
Well, this works, its a bit manual:
data list; input name$ date; informat date date9.; format date date9.; datalines; abc 1MAR2017 ab2 2MAR2017 ab3 3MAR2017 ab4 4MAR2017 ab5 5MAR2017 ab6 6MAR2017 ab7 7MAR2017 ab8 8MAR2017 ab9 9MAR2017 ab10 10MAR2017 ab11 11MAR2017 ab12 12MAR2017 ab13 13MAR2017 ab14 14MAR2017 ab15 15MAR2017 ab16 16MAR2017 ab17 17MAR2017 ab18 18MAR2017 ab19 19MAR2017 ab20 20MAR2017 ab21 21MAR2017 ab22 22MAR2017 ab23 23MAR2017 ab24 24MAR2017 ab25 25MAR2017 ab26 26MAR2017 ab27 27MAR2017 ab28 28MAR2017 ab29 29MAR2017 ab30 30MAR2017 ab31 31MAR2017 ; run; data holidays; input date1 date9.; informat date1 date9.; format date1 date9.; datalines; 05MAR2017 11MAR2017 12MAR2017 15MAR2017 19MAR2017 24MAR2017 25MAR2017 26MAR2017 ; run; data want (drop=hols); merge list (in=a) holidays (in=b rename=(date1=date)); by date; retain date_diff hols; if _n_=1 then hols=8; date_diff=('31mar2017'd - date) - hols; if b then hols=hols-1; run;
You have told us nothing. Examine the instructions which are found underneath the Post button on new messages. Have you provided test data (in the form of a datastep) - no, have you provided example output required - no. We cannot see your screen, you have to provide this to us for us to be able to know what your doing.
hi
My dataset is like this
data list;
input name$ date;
informat date date9.;
format date date9.;
datalines;
abc 1MAR2017
ab2 2MAR2017
ab3 3MAR2017
ab4 4MAR2017
ab5 5MAR2017
ab6 6MAR2017
ab7 7MAR2017
ab8 8MAR2017
ab9 9MAR2017
ab10 10MAR2017
ab11 11MAR2017
ab12 12MAR2017
ab13 13MAR2017
ab14 14MAR2017
ab15 15MAR2017
ab16 16MAR2017
ab17 17MAR2017
ab18 18MAR2017
ab19 19MAR2017
ab20 20MAR2017
ab21 21MAR2017
ab22 22MAR2017
ab23 23MAR2017
ab24 24MAR2017
ab25 25MAR2017
ab26 26MAR2017
ab27 27MAR2017
ab28 28MAR2017
ab29 29MAR2017
ab30 30MAR2017
ab31 31MAR2017
;
run;
/*my holidays list:*/
data holidays;
input date1 date9.;
informat date1 date9.;
format date1 date9.;
05MAR2017
11MAR2017
12MAR2017
15MAR2017
19MAR2017
24MAR2017
25MAR2017
26MAR2017
;
run;
/*my condition is date_diff="31MAR2017-date;(in list dataset) and use holidays data for excluding holidays.*/
/* I want output like this:*/
NAME DATE Date_diff
abc 1MAR2017 2 2
ab2 2MAR2017 21
ab3 3MAR2017 20
ab4 4MAR2017 19
ab5 5MAR2017 18
ab6 6MAR2017 18
ab7 7MAR2017 17
ab8 8MAR2017 16
ab9 9MAR2017 15
ab10 10MAR2017 14
ab11 11MAR2017 13
ab12 12MAR2017 13
ab13 13MAR2017 13
ab14 14MAR2017 12
ab15 15MAR2017 11
ab16 16MAR2017 11
ab17 17MAR2017 10
ab18 18MAR2017 9
ab19 19MAR2017 8
ab20 20MAR2017 8
ab21 21MAR2017 7
ab22 22MAR2017 6
ab23 23MAR2017 5
ab24 24MAR2017 4
ab25 25MAR2017 4
ab26 26MAR2017 4
ab27 27MAR2017 4
ab28 28MAR2017 3
ab29 29MAR2017 2
ab30 30MAR2017 1
ab31 31MAR2017 0
Thanks in advance..
Well, this works, its a bit manual:
data list; input name$ date; informat date date9.; format date date9.; datalines; abc 1MAR2017 ab2 2MAR2017 ab3 3MAR2017 ab4 4MAR2017 ab5 5MAR2017 ab6 6MAR2017 ab7 7MAR2017 ab8 8MAR2017 ab9 9MAR2017 ab10 10MAR2017 ab11 11MAR2017 ab12 12MAR2017 ab13 13MAR2017 ab14 14MAR2017 ab15 15MAR2017 ab16 16MAR2017 ab17 17MAR2017 ab18 18MAR2017 ab19 19MAR2017 ab20 20MAR2017 ab21 21MAR2017 ab22 22MAR2017 ab23 23MAR2017 ab24 24MAR2017 ab25 25MAR2017 ab26 26MAR2017 ab27 27MAR2017 ab28 28MAR2017 ab29 29MAR2017 ab30 30MAR2017 ab31 31MAR2017 ; run; data holidays; input date1 date9.; informat date1 date9.; format date1 date9.; datalines; 05MAR2017 11MAR2017 12MAR2017 15MAR2017 19MAR2017 24MAR2017 25MAR2017 26MAR2017 ; run; data want (drop=hols); merge list (in=a) holidays (in=b rename=(date1=date)); by date; retain date_diff hols; if _n_=1 then hols=8; date_diff=('31mar2017'd - date) - hols; if b then hols=hols-1; run;
Yes it is working in SAS EG
but if i take more holidays
for ex my list data is same
data list; input name$ date; informat date date9.; format date date9.; datalines; abc 1MAR2017 ab2 2MAR2017 ab3 3MAR2017 ab4 4MAR2017 ab5 5MAR2017 ab6 6MAR2017 ab7 7MAR2017 ab8 8MAR2017 ab9 9MAR2017 ab10 10MAR2017 ab11 11MAR2017 ab12 12MAR2017 ab13 13MAR2017 ab14 14MAR2017 ab15 15MAR2017 ab16 16MAR2017 ab17 17MAR2017 ab18 18MAR2017 ab19 19MAR2017 ab20 20MAR2017 ab21 21MAR2017 ab22 22MAR2017 ab23 23MAR2017 ab24 24MAR2017 ab25 25MAR2017 ab26 26MAR2017 ab27 27MAR2017 ab28 28MAR2017 ab29 29MAR2017 ab30 30MAR2017 ab31 31MAR2017 ; run;
and now my holidays list is like this
data holidays; input date1 date9.; informat date1 date9.;
format date1 date9.;
datalines;
11FEB2017
12FEB2017
19FEB2017
24FEB2017
25FEB2017
26FEB2017
05MAR2017
11MAR2017
12MAR2017
15MAR2017
19MAR2017
24MAR2017
25MAR2017
26MAR2017
02APR2017
04APR2017
08APR2017
09APR2017
14APR2017
16APR2017
22APR2017
23APR2017
30APR2017
07MAY2017
14MAY2017
21MAY2017
28MAY2017
04JUN2017
11JUN2017
18JUN2017
25JUN2017
02JUL2017
09JUL2017
16JUL2017
23JUL2017
30JUL2017
06AUG2017
13AUG2017
20AUG2017
27AUG2017
03SEP2017
10SEP2017
17SEP2017
24SEP2017
01OCT2017
08OCT2017
15OCT2017
22OCT2017
29OCT2017
05NOV2017
12NOV2017
19NOV2017
26NOV2017
03DEC2017
10DEC2017
17DEC2017
24DEC2017
31DEC2017
07JAN2018
14JAN2018
21JAN2018
26JAN2018
28JAN2018
04FEB2018
11FEB2018
18FEB2018
25FEB2018
04MAR2018
11MAR2018
and now i want output like this
NAME DATE Date_diff
abc 1MAR2017 2 2
ab2 2MAR2017 21
ab3 3MAR2017 20
ab4 4MAR2017 19
ab5 5MAR2017 18
ab6 6MAR2017 18
ab7 7MAR2017 17
ab8 8MAR2017 16
ab9 9MAR2017 15
ab10 10MAR2017 14
ab11 11MAR2017 13
ab12 12MAR2017 13
ab13 13MAR2017 13
ab14 14MAR2017 12
ab15 15MAR2017 11
ab16 16MAR2017 11
ab17 17MAR2017 10
ab18 18MAR2017 9
ab19 19MAR2017 8
ab20 20MAR2017 8
ab21 21MAR2017 7
ab22 22MAR2017 6
ab23 23MAR2017 5
ab24 24MAR2017 4
ab25 25MAR2017 4
ab26 26MAR2017 4
ab27 27MAR2017 4
ab28 28MAR2017 3
ab29 29MAR2017 2
ab30 30MAR2017 1
ab31 31MAR2017 0
Kindly Resolve
Thanks in advance
I do not know what "it is not working" is? Does the code not run, is there errors, what?
It doesn't matter how many rows you add in holidays - the core data lists remains the same, so only March values will be used from holidays. If this is not the case, please illustrate exactly what you mean.
Hi,
If i take more holidays (last holidays file)
I got this output.
name date date_diff
11FEB2017 40
12FEB2017 40
19FEB2017 34
24FEB2017 30
25FEB2017 30
26FEB2017 30
abc 01MAR2017 28
ab2 02MAR2017 27
ab3 03MAR2017 26
ab4 04MAR2017 25
ab5 05MAR2017 24
ab6 06MAR2017 24
ab7 07MAR2017 23
ab8 08MAR2017 22
ab9 09MAR2017 21
ab10 10MAR2017 20
ab11 11MAR2017 19
ab12 12MAR2017 19
ab13 13MAR2017 19
ab14 14MAR2017 18
ab15 15MAR2017 17
ab16 16MAR2017 17
ab17 17MAR2017 16
ab18 18MAR2017 15
ab19 19MAR2017 14
ab20 20MAR2017 14
ab21 21MAR2017 13
ab22 22MAR2017 12
ab23 23MAR2017 11
ab24 24MAR2017 10
ab25 25MAR2017 10
ab26 26MAR2017 10
ab27 27MAR2017 10
ab28 28MAR2017 9
ab29 29MAR2017 8
ab30 30MAR2017 7
ab31 31MAR2017 6
02APR2017 4
04APR2017 3
08APR2017 0
09APR2017 0
14APR2017 -4
16APR2017 -5
22APR2017 -10
23APR2017 -10
30APR2017 -16
07MAY2017 -22
14MAY2017 -28
21MAY2017 -34
28MAY2017 -40
04JUN2017 -46
11JUN2017 -52
18JUN2017 -58
25JUN2017 -64
02JUL2017 -70
09JUL2017 -76
16JUL2017 -82
23JUL2017 -88
30JUL2017 -94
06AUG2017 -100
13AUG2017 -106
20AUG2017 -112
27AUG2017 -118
03SEP2017 -124
10SEP2017 -130
17SEP2017 -136
24SEP2017 -142
01OCT2017 -148
08OCT2017 -154
15OCT2017 -160
22OCT2017 -166
29OCT2017 -172
05NOV2017 -178
12NOV2017 -184
19NOV2017 -190
26NOV2017 -196
03DEC2017 -202
10DEC2017 -208
17DEC2017 -214
24DEC2017 -220
31DEC2017 -226
07JAN2018 -232
14JAN2018 -238
21JAN2018 -244
26JAN2018 -248
28JAN2018 -249
04FEB2018 -255
11FEB2018 -261
18FEB2018 -267
25FEB2018 -273
04MAR2018 -279
11MAR2018 -285
if I take more holidays it gives wrong output
Please check the last holidays list and above output
Just add:
data want (drop=hols); merge list (in=a) holidays (in=b rename=(date1=date)); by date; retain date_diff hols; if _n_=1 then hols=8; date_diff=('31mar2017'd - date) - hols; if b then hols=hols-1;
if a then output; run;
Hi
your last code gives wrong output
the code is
data want (drop=hols);
merge list (in=a) hol1 (in=b rename=(date1=date));
by date;
retain date_diff hols;
if _n_=1 then hols=18;
date_diff=('31mar2017'd - date) - hols;
if b then hols=hols-1;
if a then output;
run;
The output is
01MAR2017 12
02MAR2017 11
03MAR2017 10
04MAR2017 9
05MAR2017 8
06MAR2017 8
07MAR2017 7
08MAR2017 6
09MAR2017 5
ab10 10MAR2017 4
ab11 11MAR2017 3
ab12 12MAR2017 3
ab1a 12MAR2017 4
ab1B 12MAR2017 5
ab1c 12MAR2017 6
ab13 13MAR2017 6
ab14 14MAR2017 5
ab15 15MAR2017 4
ab16 16MAR2017 4
ab17 17MAR2017 3
ab18 18MAR2017 2
ab19 19MAR2017 1
ab20 20MAR2017 1
ab2a 20MAR2017 2
ab2b 20MAR2017 3
ab2c 20MAR2017 4
ab2d 20MAR2017 5
ab21 21MAR2017 5
ab22 22MAR2017 5
ab23 23MAR2017 5
ab24 24MAR2017 4
ab25 25MAR2017 4
ab26 26MAR2017 4
ab27 27MAR2017 4
ab28 28MAR2017 3
ab29 29MAR2017 2
ab30 30MAR2017 1
ab31 31MAR2017 0
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.