Hi All,
I have a data set as shown below. (Actual data set contains more records)
city sol_id date1 date2
Kochi 0010 12mar2011 30may2011
Kochi 0011 07Apr2011 10jun2011
Delhi 0567 23feb2011 20jun2011
Mumbai 0710 17may2011 18may2011
Cherai 0110 12jan2011 26may2011
Cherai 0510 03jun2011 16jun2011
And i have the list of holidays peratining to each city in another data set like this.
city holiday
Kochi 16mar2011
Kochi 02may2011
kochi 03may2011
Delhi 01jan2011
Delhi 28feb2011
Delhi 16jun2011
Mumbai 01jan2011
Mumbai 09jan2011
Cherai 12jan2011
Cherai 13feb2011
Cherai 06jun2011
Cherai 11jun2011
I wanna find out the the number of working days between date1 and date2 from the first data set excluding all sundays as well as all holidays falling in between from the second data set..
I would prefer to have a macro as my both tables contains more number of records(cities).
Thanks in advance..
This article talks about using proc FCMP to do this, using a dataset of holidays. I guess it could be adapted to also allow you to specfiy a city/location as part of the calculation. You'd need to be running 9.2 as well of course! (If not the logic can likely be replicated in a data step, and/or via a macro).
http://sas-x.com/2011/05/calculating-the-number-of-working-days-between-two-dates/
HTH.
There are no need to use macro for your simple situation, I think .
data date; input city $ sol_id $ date1 : date9. date2 : date9.; format date1 date2 date9.; datalines; Kochi 0010 12mar2011 30may2011 Kochi 0011 07Apr2011 10jun2011 Delhi 0567 23feb2011 20jun2011 Mumbai 0710 17may2011 18may2011 Cherai 0110 12jan2011 26may2011 Cherai 0510 03jun2011 16jun2011 ; run; data holiday; input city $ holiday : date9.; format holiday date9.; datalines; Kochi 16mar2011 Kochi 02may2011 Kochi 03may2011 Delhi 01jan2011 Delhi 28feb2011 Delhi 16jun2011 Mumbai 01jan2011 Mumbai 09jan2011 Cherai 12jan2011 Cherai 13feb2011 Cherai 06jun2011 Cherai 11jun2011 ; run; data temp; set date; num_holiday=0; do i=1 to _nobs; set holiday(rename=(city=_city)) nobs=_nobs point=i; if city eq _city and holiday ge date1 and holiday le date2 then num_holiday+1; if i=_nobs then output; end; drop i _city holiday; run; data want; set temp; num_workday=date2-date1-num_holiday+1; run;
Ksharp
Hi Ksharp
The OT asked for the number of working days for a 6 days week only (excluding Sunday).
Assuming that working days are from Monday to Friday and that the holiday data set has no days on a Sun wouldn't the calculation in your code be:
num_workday=intck('weeday1w',date1,date2)-num_holiday;
Cheers
Patrick
why is no one suggesting INTERVALDS option where you can specify a data set that defines your custom interval.?
Hi Peter
Can you give us an example to display how to use the second dataset as a inter-range dataset to achieve it.
It is very interesting .I think.
Cheers
Ksharp
This program is copied from the example of counting days omitting holidays found here http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_intervals_sect...
The only difference is this program creates 4 custom intervals instead of one. I use a data step and hash to create all four custom intervals in one step.
dm 'clear log; clear output';
proc datasets kill;
run;
data range;
input (city sol_id)($) (date1 date2)(:date.);
format date: weekdate.;
cards;
Kochi 0010 12mar2011 30may2011
Kochi 0011 07Apr2011 10jun2011
Kochi 0011 16mar2011 17mar2011
Kochi 0011 16mar2011 16mar2011
Kochi 0011 17mar2011 18mar2011
Kochi 0011 17mar2011 17mar2011
Delhi 0567 23feb2011 20jun2011
Mumbai 0710 17may2011 18may2011
Cherai 0110 12jan2011 26may2011
Cherai 0510 03jun2011 16jun2011
;;;;
run;
data holiday;
input city $ holiday :date.;
format holiday date9.;
cards;
Kochi 16mar2011
Kochi 02may2011
Kochi 03may2011
Delhi 01jan2011
Delhi 28feb2011
Delhi 16jun2011
Mumbai 01jan2011
Mumbai 09jan2011
Cherai 12jan2011
Cherai 13feb2011
Cherai 06jun2011
Cherai 11jun2011
;;;;
run;
*** Create custom interval data sets;
data _null_;
if 0 then set holiday;
** Lookup table for holidays by city;
declare hash holidayH(dataset:'Holiday',ordered:'Y');
holidayH.definekey('city','holiday');
holidayH.definedata('city','holiday');
holidayH.definedone();
holidayH.output(dataset:'holicity');
** List of CITYs, really just a fancy array;
declare hash cityH(dataset:'Holiday',ordered:'Y');
cityH.definekey('city');
cityH.definedata('city');
cityH.definedone();
cityH.output(dataset:'city');
declare hiter cityITER('cityH');
** Output: interval datasets one for each CITY/ custom interval;
attrib begin length=8 format=weekdate.;
declare hash intervalds(ordered:'Y');
intervalds.definekey('begin');
intervalds.definedata('begin');
intervalds.definedone();
attrib intervaldsOPTION length=$256;
do rc=cityITER.next() by 0 while(rc eq 0);
start = '01jan2011'd;
end = '30jun2011'd;
do begin = start to end;
isHoliday = not holidayH.check(key:city,key:begin);
isWeekend = weekday(begin) in(1,7);
if not(isHoliday or isWeekend) then rc=intervalds.add();
end;
intervaldsOPTION = catx(' ',intervaldsOPTION,catx('=',city,city));
intervalds.output(dataset:city);
intervalds.clear();
rc=cityITER.next();
end;
put intervaldsOPTION=;
call execute(cats('options intervalds=(',intervaldsOPTION,');'));
stop;
run;
proc options option=intervalds;
run;
proc report data=kochi panels=3 nowd;
define begin / display;
run;
data range2;
set range;
daysSpecial = intck(city,date1,date2);
days = intck('DAY',date1,date2);
WeekDays = intck('WEEKDAY',date1,date2);
run;
proc print;
run;
I am also not familiar with this method.I need some more time to digest. Thanks your code.
Ksharp
I have to share my script written for SAS 9.2 with a colleague who is running SAS 9.1.3.
And therefore I need an equivalent of INTCK("weekday", start_date, end_date);
The above code looks complicated to me. How would I modify the above just to incorporate counting the number of days between two dates excluding weekends?
Thanks.
Are you saying that WEEKDAY is not a valid interval in SAS 9.1.3.
The example program I wrote for this thread uses a custom interval which I don't think applies to your question regarding INTCK('WEEKDAY'
I prefer to Hash Table.
Hi Patrick.
Yes. I did not consider this situation, thanks for your reminder.
But if OP want ,he or she also can add Sunday , Saturday into the second dataset ,you see?
Cheers!
Ksharp
Hi
I want exclude holidays from a holidays list(dataset)
if it is possible Kindly explain
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: