BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bollurajkumar
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

29 REPLIES 29
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

bollurajkumar
Fluorite | Level 6

 

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..

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
bollurajkumar
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I do not know what "it is not working" is?  Does the code not run, is there errors, what?

bollurajkumar
Fluorite | Level 6
sorry
it gives correct output
bollurajkumar
Fluorite | Level 6
but if i take more holidays how can i write code
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

bollurajkumar
Fluorite | Level 6

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

bollurajkumar
Fluorite | Level 6

if I take more holidays it gives wrong output
Please check the last holidays list and above output

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
bollurajkumar
Fluorite | Level 6

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

bollurajkumar
Fluorite | Level 6
I got correct answer for this code
thanks for spending your time on this question
bollurajkumar
Fluorite | Level 6

@RW9

I got correct answer for your  code
thanks for spending your time on this question

 

thanx

Rajkumar

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 29 replies
  • 1518 views
  • 4 likes
  • 5 in conversation