topic sas eg in SAS Programming

sas eg

bollurajkumar — Thu, 20 Apr 2017 12:55:35 GMT

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

Re: sas eg

RW9 — Thu, 20 Apr 2017 12:58:35 GMT

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.

Re: sas eg

Reeza — Thu, 20 Apr 2017 20:52:14 GMT

What do you mean by exclude?

Are you removing records from your dataset?

Or are you trying to calculate a duration and don't want to include the holidays in the calculation.

Please review the following post on how to ask a good question. As formulated, your question is ambiguous and doesn't have relevant information. So rather than spending time answering your question we're trying to figure it out first. This is a waste of everyone's time, including yours and it takes longer for you to get an answer. And some people get mad/rude 🙂

https://stackoverflow.com/help/how-to-ask

Re: sas eg

bollurajkumar — Fri, 21 Apr 2017 06:51:57 GMT

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

Re: sas eg

RW9 — Fri, 21 Apr 2017 08:25:04 GMT

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;

Re: sas eg

bollurajkumar — Fri, 21 Apr 2017 12:48:12 GMT

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

Kindly Resolve

Thanks in advance

Re: sas eg

RW9 — Fri, 21 Apr 2017 12:36:02 GMT

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

Re: sas eg

bollurajkumar — Fri, 21 Apr 2017 12:53:36 GMT

sorry
it gives correct output

Re: sas eg

bollurajkumar — Fri, 21 Apr 2017 12:54:40 GMT

but if i take more holidays how can i write code

Re: sas eg

RW9 — Fri, 21 Apr 2017 13:19:27 GMT

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.

Re: sas eg

bollurajkumar — Mon, 24 Apr 2017 04:45:47 GMT

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

Re: sas eg

bollurajkumar — Mon, 24 Apr 2017 06:08:26 GMT

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

Date difference in days excluding holidays

bollurajkumar — Mon, 24 Apr 2017 06:53:48 GMT

Hi All,

I have a data set as shown below. (Actual data set contains more records)

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
ab1a 12MAR2017
ab1B 12MAR2017
ab1c 12MAR2017
ab13 13MAR2017
ab14 14MAR2017
ab15 15MAR2017
ab16 16MAR2017
ab17 17MAR2017
ab18 18MAR2017
ab19 19MAR2017
ab20 20MAR2017
ab2a 20MAR2017
ab2b 20MAR2017
ab2c 20MAR2017
ab2d 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.;
datalines;
05MAR2017
11MAR2017
12MAR2017
15MAR2017
19MAR2017
20MAR2017
21MAR2017
22MAR2017
24MAR2017
25MAR2017
26MAR2017
run;

my condition is date_diff="31MAR2017-date;(in list dataset) and use holidays data for exclude holidays.

I want output like this:

NAME DATE     Date_diff

abc 1MAR2017     19
ab2 2MAR2017     18
ab3 3MAR2017     17
ab4 4MAR2017     16
ab5 5MAR2017     15
ab6 6MAR2017     15
ab7 7MAR2017     14
ab8 8MAR2017     13
ab9 9MAR2017     12
ab10 10MAR2017   11
ab11 11MAR2017   10
ab12 12MAR2017   10
ab1a 12MAR2017   10
ab1B 12MAR2017   10
ab1c 12MAR2017   10
ab13 13MAR2017   10
ab14 14MAR2017   9
ab15 15MAR2017   8
ab16 16MAR2017   8
ab17 17MAR2017   7
ab18 18MAR2017   6
ab19 19MAR2017   5
ab20 20MAR2017   5
ab2a 20MAR2017   5
ab2b 20MAR2017   5
ab2c 20MAR2017   5
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

Thanks in advance..

Re: Date difference in days excluding holidays

andreas_lds — Mon, 24 Apr 2017 07:33:21 GMT

Seems to be a clone of https://communities.sas.com/t5/Base-SAS-Programming/sas-eg/td-p/351650.

Please post code using the "running-man-icon".
Please post the code you already have written.
The condition "date_diff="31MAR2017-date" seems to depend on the data ...

Re: sas eg

RW9 — Mon, 24 Apr 2017 08:19:04 GMT

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;

Re: sas eg

bollurajkumar — Mon, 24 Apr 2017 09:37:07 GMT

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

Re: Date difference in days excluding holidays

bollurajkumar — Mon, 24 Apr 2017 09:29:36 GMT

I gave you perfect data

Re: Date difference in days excluding holidays

RW9 — Mon, 24 Apr 2017 09:36:20 GMT

Then please post, exactly what data your working with and what code as when I run the below it works fine and doesn't have extra rows.

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;
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
;
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;
  if a then output;
run;

Re: Date difference in days excluding holidays

bollurajkumar — Mon, 24 Apr 2017 09:56:37 GMT

but the count is wrong please check it

and if i use same date multiple time it will not give correct count

My input is

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
ab1a 12MAR2017
ab1B 12MAR2017
ab1c 12MAR2017
ab13 13MAR2017
ab14 14MAR2017
ab15 15MAR2017
ab16 16MAR2017
ab17 17MAR2017
ab18 18MAR2017
ab19 19MAR2017
ab20 20MAR2017
ab2a 20MAR2017
ab2b 20MAR2017
ab2c 20MAR2017
ab2d 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 my holiday list is

data holidays;
  input date1 date9.;
  informat date1 date9.;
  format date1 date9.;
datalines;
05MAR2017
11MAR2017
12MAR2017
15MAR2017
19MAR2017
20MAR2017
21MAR2017
22MAR2017
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
run;

my condition is date_diff="31MAR2017"d-date;(in list dataset) and use holidays data for exclude holidays.

i want this output

*******************************************************

************************************

Thanks in advance..

Re: Date difference in days excluding holidays

Patrick — Mon, 24 Apr 2017 12:14:51 GMT

@bollurajkumar

You can also create a data set with the dates you want (i.e. workingdays) and then use this data set as input for a custom interval.

This is what's done in below code.

/* your lakh of source data */
data have;
  format myDate date9.;
  do myDate='01Mar2017'd to '31Mar2017'd;
    output;
  end;
  stop;
run;

/* list of holiday dates */
data holidays;
  input date1 date9.;
  informat date1 date9.;
  format date1 date9.;
  datalines;
05MAR2017
11MAR2017
12MAR2017
15MAR2017
19MAR2017
20MAR2017
21MAR2017
22MAR2017
24MAR2017
25MAR2017
26MAR2017
;
run;


/* Create working date table as input for custom interval:
    - only dates from Monday to Friday
    - all dates in work.holidays excluded
   In this sample only year 2017 covered. Ensure to build up a
   table which covers the whole date range required for your source table 
*/ 
data workingdays;
  format begin date9.;
  dcl hash hHolidays(dataset:'holidays(keep=date1 rename=(date1=begin))');
  hHolidays.defineKey('begin');
  hHolidays.defineDone();
  do begin='01jan2017'd to '31dec2017'd;
    if weekday(begin) in (1,7) then continue; /* exclude Saturday & Sunday */
    if hHolidays.check() = 0   then continue; /* exclude dates from ds "holiday" */
    output;
  end;
  stop;
run;

/* use work.workingdays as input to custom interval */
options intervalds=(intWorkingdays=work.workingdays);

/* calculate date difference using SAS calendar function intck() with custom interval */
data want;
  set have;
  date_diff=intck('intWorkingdays',myDate,'31mar2017'd);
run;

Just make sure you've got only the dates you want to use for your "count" in data set workingdays and amend the code I've posted accordingly (i.e. by removing the exclusion logic for week ends).

The code will work if you get the data for workingdays right so please don't just come back and tell us "it's not working, please fix". It's now up to you to finish this off.

In case custom intervals are new to you then here a starting point for skilling-up:
http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p10v3sa3i4kfxfn1sovhi5xzxh8n.htm