Count the Number of Days of the Week Between Two Dates

[griffinh]

Desired OutputHello SAS Gurus!

I've searched for the answer for this with no luck. I am currently using SAS Enterprise Guide 7.15.

I have a distinct list of courses each term. I am trying to count the number of times the class meets within the start/end date based on the specified days of the week. The snip with yellow shows my desired output. I would be so grateful for any ideas of how to accomplish this. Thank you!

Current SAS Output

[PaigeMiller]

Many of us refuse to download Microsoft Office documents as a security threat. You can make a screen capture of your desired output, and include it in your reply using the "Insert Photos" icon.

[griffinh]

Thanks so much for explaining this. I have updated the post. 😊

[PaigeMiller]

Please explain how you arrive at an answer of 15 for the first row and an answer of 34 in the second row.

[griffinh]

Certainly, thanks for your help.

The 1st row meets every Tuesday and Thursday between January 10, 2022 and March 1, 2022. I counted the number of Tuesdays and Thursdays in this date range to arrive at 15. 

The 2nd row meets every Monday and Wednesday between January 10, 2022 and May 5, 2022. I counted the number of Mondays and Wednesdays in this date range to arrive at 34.

Thank you

[Reeza]

For the first row, is the logic find the number of Tuesday/Thursdays between Jan 10, 2022 and March 1, 2022?
And do you need to account for holidays - courses that are on Mondays are more likely to be affected in those cases.

[griffinh]

Yes, that logic is correct: The 1st row meets every Tuesday and Thursday between January 10, 2022 and March 1, 2022. I counted the number of Tuesdays and Thursdays in this date range to arrive at 15. 

You make a great point, I had not considered holidays. Standard US Holidays should be excluded from the count if this is possible. If not, that's ok. Thanks for your guidance!

[PaigeMiller]

What does the number 3 under Tuesday and 5 under Thursday in the first row have to do with this?

[griffinh]

Originally I was trying to assign each weekday it's numerical equivalent in SAS and somehow create some sort of crosswalk to assign these days to the dates. This didn't end up working and I just never removed it. I do not need those columns at all. Great catch, thanks!

[Tom]

---

@griffinh wrote:

Originally I was trying to assign each weekday it's numerical equivalent in SAS and somehow create some sort of crosswalk to assign these days to the dates. This didn't end up working and I just never removed it. I do not need those columns at all. Great catch, thanks!

---

They are actually more useful than the column with the letters.

total=0;
do day = startdate to enddate ;
   total + ( 0 <  whichn(weekday(day), of sunday -- saturday) );
end;

[griffinh]

Interesting! Thanks for this piece of code. I am a total newbie so bear with me. My source data comes from work.setup_meets and my output is named work.count_meets. Here is the code I used and the result. Any ideas on where I went wrong here? Thanks for your guidance!

data work.count_meets;

set work.setup_meets;

total=0;
do day = bannerstartdate to bannerenddate ;
   total + ( 0 <  whichn(weekday(day), '1','2','3','4','5','6','7') );
end;

run;

Output

[Tom]

You should have gotten a lot of notes about having to convert the strings like '1' and '2' to numbers to use with the whichN function.  Since WEEKDAY is by definition going to return a number between 1 and 7 all you really did was count all of the days.  You could have done that with a simple subtraction.

The idea of using the existing SUNDAY, MONDAY,... SATURDAY variable is they have zeros for the days you want to ignore. So WHICHN() will return zero when it cannot find the number.  For example on the first observation SUNDAY is zero.  So when WEEKDAY(day) is 1 (that is on a SUNDAY) it does not find 1 in the list of values so the TOTAL does not increment.

If you want better code then post example data, as a program that re-creates the data.

[griffinh]

Thanks for explaining this Tom. I will try to recreate this data in a program to share. Like I said, I am new, but I'll give it a shot. I appreciate your guidance.

[s_lassen]

If you need to account for holidays, you will have to run through all the dates, like this:

data want;                                     
  set have;                                    
  array week(*) sunday--saturday;              
  days=0;                                      
  do _N_=bannerStartDate to bannerEndDate;     
    if week(weekday(_N_)) then do;             
      holiday=holidayname(_N_);                
      if holiday ne ' ' then put               
        _N_ date9. ' skipped: ' holiday;       
      else days+1;                             
      end;                                     
    end;                                       
run;

I put code in to show which holidays were skipped, I am not sure students get a day off on Valentines day. So you may want to expand the condition on holiday, e.g.

if holiday not in(' ','VALENTINES') then put

You may have to use the LOCALE argument for the HOLIDAYNAME function if you do not live in the States.

[Reeza]

These are super ugly solutions so hopefully someone else has a better answer. 

First, create a list of dates that are holidays that you need to account for and a list of all dates. You can customize this to be just for your period of interest. 

Starter code for Holidays from here 

%let year_start = 2022;
%let year_end = 2022;
data holidays ;
length HolidayName $ 30 ;
array WkDayShift [7] _temporary_ ( 1 5*0 -1 ) ;
retain ShiftToggle /* 0 */ 1 ;
do Year = &year_start to &year_end ;
 
   HolidayName = "New Year's Day" ;
   * 1 Jan if not Mon 2 Jan. ;
   HoliDate = holiday('newyear', year) ;
   dow = weekday(HoliDate) ;
   HoliDate = intnx( 'day', HoliDate, WkDayShift[dow] * ShiftToggle ) ;
   if not ( (dow EQ 7) and ShiftToggle ) then output ;
 
   HolidayName = "Martin Luther King Day" ;
   * 3rd Mon in Jan ;
   HoliDate = holiday('mlk', year) ;
   output ; 	
 
   HolidayName = "Presidents' Day" ;
   * 3rd Mon in Feb ;
   HoliDate = holiday('uspresidents', year) ;
   output ; 
 
   HolidayName = "Memorial Day" ;
   * Last Mon in May ;
   HoliDate = holiday('memorial', year) ;
   output ;
 
   HolidayName = "Independence Day" ;
   * 4 Jul if not Mon 5 Jul or Fri 3 Jul ;
   HoliDate = holiday('usindependence',year) ;
   HoliDate = intnx('day',
                    HoliDate,
                    WkDayShift[ weekday(HoliDate) ] * ShiftToggle) ;
   output ;	
 
   HolidayName = "Labor Day" ;
   * 1st Mon in Sep ;
   HoliDate = holiday('labor', year) ;
   output ;
 
   HolidayName = "Columbus Day" ;
   * 2nd Mon in Oct ;
   HoliDate = holiday('columbus', year) ;
   output ;	
 
   HolidayName = "Veterans Day" ;
   *11 Nov if not Mon 12 Nov or Fri 10 Nov ;
   HoliDate = holiday(ifc(ShiftToggle, 'veteransusg', 'veterans'), year) ;
   output ;
 
   HolidayName = "Thanksgiving Day" ;
   *4th Thu in Nov ;
   HoliDate =  holiday('thanksgiving', year) ;
   output ;
 
   HolidayName = "Christmas" ;
   * 25 Dec if not Mon 26 Dec or Fri 24 Dec ;
   HoliDate = holiday('christmas', year) ; 
   HoliDate = intnx('day',
                    HoliDate,
                    WkDayShift[ weekday(HoliDate) ] * ShiftToggle) ;
   output ;
 
   HolidayName = "New Year's Eve" ;
   * Fri 31 Dec or not at all ;
   HoliDate = mdy(12, 31, year) ;
   if weekday(HoliDate) EQ 6 and ShiftToggle then output ;
 
   end ;
 
keep HolidayName HoliDate ;
rename holidate= date;
format holidate date9. ;

run ;

data dates;
array Day(7) ;
do date=mdy(1, 1, &year_start) to mdy(12, 31, &year_end);
do i=1 to 7;
day(i) = 0;
end;
day(weekday(date)) = 1;
output;
end;
drop i;
run;

data date_dim;
merge dates holidays(in=hol);
by date;
holiday = hol;
run;

Creation of sample data (please provide as text in the future). 

data have;
informat ID $1. bannerSTartDate bannerendDate date9.;
format ID $1. bannerSTartDate bannerendDate date9.;
input ID bannerStartDate bannerEndDate Sunday Monday Tuesday Wednesday Thursday Friday Saturday;
cards;
1 10Jan2022 01Mar2022 0 0 3 0 4 0 0
2 10Jan2022 05May2022 0 2 0 4 0 0 0
;;;;
run;

Then you can merge this data with the date dimension and sum the number of each weekday excluding holidays. To get the total number of days it's essentially a multiplication then, of Sunday*# of sundays in interval where Sunday is an indicator to include that variable. There may be an easier way of doing this last step but not sure. 

proc sql;
create table summary as 
select id, bannerStartDate, bannerEndDate
,sum(day1)*(max(Sunday)>0) as D1
,sum(day2)*(max(Monday)>0) as D2
,sum(day3)*(max(Tuesday)>0) as D3
,sum(day4)*(max(Wednesday)>0) as D4
,sum(day5)*(max(Thursday)>0) as D5
,sum(day6)*(max(Friday)>0) as D6
,sum(day7)*(max(Saturday)>0) as D7
,sum(calculated D1, calculated D2, calculated D3, calculated D4, calculated D5, calculated D6, calculated D7)

as Num_days
from have as h
left join date_dim as dd on 
dd.date between bannerStartDate and bannerEndDate
where holiday = 0
group by 1, 2, 3 ;
quit;

An alternative method is to use @Tom concise and elegant solution and add in a check for holidays. 

proc sql noprint;
select date format=8. into: holiday_list separated by ", "
from holidays;
quit;

%put &holiday_list;


data want;
set have;

total=0;

array _week(7) sunday--saturday;

do i=1 to dim (_week);
   if _week(i)>0 then _week(i) = 1;
end;

do day = bannerSTartDate to bannerendDate ;
      total + _week(weekday(day))*(1- (whichn(day, &holiday_list)>0));
      *output;
end;
format day date9.;
run;

---

@griffinh wrote:

Desired OutputHello SAS Gurus!

I've searched for the answer for this with no luck. I am currently using SAS Enterprise Guide 7.15.

I have a distinct list of courses each term. I am trying to count the number of times the class meets within the start/end date based on the specified days of the week. The snip with yellow shows my desired output. I would be so grateful for any ideas of how to accomplish this. Thank you!

Current SAS Output

---