sas eg

[Reeza]

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

[bollurajkumar]

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

[bollurajkumar]

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

[andreas_lds]

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

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

[bollurajkumar]

hi

I gave you perfect data

[RW9]

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;

[bollurajkumar]

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 

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

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

[Patrick]

@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#p10v3sa3i4k...

[bollurajkumar]

in Banking sector 1st and 3rd and/or 5th saturday is working day

2nd and 4th saturday holiday

how can i exclude  2nd and 4th saturday 

and once recheck my input list,holiday list and output

[Reeza]

Add the dates to your holiday list. 

[bollurajkumar]

input data

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;

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

Kidly check

[andreas_lds]

What should be checked?

[Patrick]

@bollurajkumar

Except from providing sample data you haven't demonstrated any own thinking and coding work despite having got already more than one working solution which simply need a bit of amendment to work with your exact variable and table names.

If you really want someone doing all the work for you then I suggest you hire someone. Else: Please demonstrate some own effort! Use one of the code versions already posted and make it work for you. Come back here if you're not able to amend them to your environment. But come back with targeted questions which demonstrate what you've done code wise, tell us exactly what's not working and where you think the problem lies (so you've actually investigated the code) and don't just repost your data over and over again.

[bollurajkumar]

@Patrick

see patrick for your undersatand i sent you data 

i got output for using below code

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;

but if i use more holidays(in data 2017-2018) or 

if i use same date for multiple times the correct output will not come 

and some members confuses about data

so i use same data for two-three times

[Patrick]

@bollurajkumar

Just re-posting code someone already provided doesn't demonstrate your own work.

But let me be nice this time. I still remember how confused I've been sometimes when I first started with programming.

The first thing you need to do is to clearly formulate and articulate the problem you want to solve. What you have, what you need, what the business rules are to get there. This is something you need to do before you even write the first line of code.

I believe a lot of the confusion is happening because you haven't been able so far to clearly explain us the business rules (the logic to be used).

So what is this about?

1. You've got a "large" source table with dates - right?

2. You've got a reference table with dates which are holidays - right?

3. You want to add a column to your large source table which contains a count of remaining working days - right?

Now: What are the business rules for this count of remaining working days? Let me guess:

a) All dates that are not in the holidays reference table - right?

b) Count per month so last day of month gets always a count of zero - right?

I believe it's the unclarity around business rule b) which causes all this confusion. So instead of just telling us "it's not working, it returns the wrong result etc." you need to be more specific and tell us why the results don't meet your rules and requirements.  

You had in your post:

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

So '31Mar2017'd is what people used in their code propositions. But that's a constant date good for the sample code you've posted but it doesn't work with other months in case your business rule isn't "count of remaining business days until 31Mar2017".

Please confirm or amend the rules I've posted and extend with any other rule which also needs to be covererd. Be as clear and specific as you can because if you can't write down the rules in a logical way then you can't write the code to implement these rules.

Thanks,

Patrick

[bollurajkumar]

@Patrick

My clarification for your queries.

1. You've got a "large" source table with dates - right?   yes

2. You've got a reference table with dates which are holidays - yes

3. You want to add a column to your large source table which contains a count of remaining working days - yes

i have a large dataset it contains 2 lack observations and around 150 variables. In this job date variables are there.

in date variable date  count i want(31MAR2017-date) without repeating the count is correct but my values are repeating so my count is decreasing 1 for every repeating value,it effect to all other values,so i need to change code

for your conformation

a) All dates that are not in the holidays reference table - yes

b) Count per month so last day of month gets always a count of zero - yes

thanks

Rajkumar