Overview

Occasionally, you might want to perform a table lookup in SAS Enterprise Guide. This sample explains how to perform a lookup on a range of dates in order to determine how many workdays are in the range.

About the Sample Data

This sample includes the code to create two data sets: CALENDAR and DATERANGES.

The code to generate the CALENDAR data set is located on the Full Code tab. CALENDAR contains a list of calendar days and their classifications (workday, weekend, holiday).

The code to create the DATERANGES data set is below. The DATERANGES data set includes start and end dates.

data DateRanges;
  input startdt :mmddyy8. stopdt :mmddyy8.;
  format startdt stopdt mmddyy8.;
cards;
01/01/09 01/06/09
02/09/09 02/12/09 
02/20/09 02/23/09 
11/22/09 11/27/09
04/30/09 05/30/09
05/22/09 05/28/09
; 
run;

Perform the Lookup

In the Query Builder, add the DATERANGES and CALENDAR data sets, and then click Join. Because the variable names do not match between the two data sets, you must manually join the data on STARTDT and DT. Right-click on the picture of the join, and then select Modify Join.

Select All rows from the left table (Left Join), and change the criteria so that DATERANGES.STARTDT <= CALENDAR.DT.

Click Close to return to the Query Builder, and then click the Filter Data tab. Add the following filter criteria:

Count the Number of Workdays

To count the number of workdays, you can remove DT and TYPE from the Select Data tab in the Query Builder, and drag over another copy of STARTDT. The copy will be named STARTDT1. You can then use the drop-down box in the Summary column and select COUNT as your summary statistic. This will change the name of STARTDT1 to COUNT_OF_STARTDT. You can rename this variable to NumberOfWorkdays.

Create a Summary Group on DATERANGES.STARTDT and DATERANGES.STOPDT, and then select Select distinct rows only.

View Results

Run the query to get an output data set with the original start date and end date values, and the number of workdays between them. Click the Results tab at the top of this sample to see the output table.

Additional Documentation

For more information about using SAS Enterprise Guide, see the SAS Enterprise Guide documentation page.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

%let start='01jan2009'd;
%let stop='31dec2009'd;

/* Create a date set for holidays.  Adjust to fit your company's needs.     */
/* Note logic is illustrated for 'static' holidays and 'observed' holidays. */

data holidays;
  length type $25;
  do year=year(&start) to year(&stop);

    /* example of 'observed' holiday logic  */
    type='New Years Day Observed';
    dt=MDY(1,1,YEAR);
    FDOY=dt;
    if weekday(dt)=1 then dt=dt+1;
    else if weekday(dt)=7 then dt=mdy(12,31,year-1);
    output;

    /* example of static holiday logic */
    type='Martin Luther King Day';
    dt=intnx('week.2',fdoy,(weekday(fdoy) ne 2)+2);
    output;

    type="Presidents Day";
    fdo_feb=intnx('month',fdoy,1);
    dt=intnx('week.2',fdo_feb,(weekday(fdo_feb) ne 2)+2);
    output;

    type='Memorial Day';
    fdo_may=intnx('month',fdoy,4);
    dt=intnx('week.2',fdo_may,(weekday(fdo_may) in (1,7))+4);
    output;

    type='Independance Day Observed';
    dt=MDY(7,4,YEAR);
    if weekday(dt)=1 then dt=dt+1;
    else if weekday(dt)=7 then dt=dt-1;
    output;

    type='Labor Day';
    fdo_sep=intnx('month',fdoy,8);
    dt=intnx('week.2',fdo_sep,(weekday(fdo_sep) ne 2));
    output;

    type='Election Day';
    fdo_nov=intnx('month',fdoy,10);
    dt=intnx('week.3',fdo_nov,1);
    output;

    type='Veterans Day Observed';
    dt=MDY(11,11,YEAR);
    if weekday(dt)=1 then dt=dt+1;
    else if weekday(dt)=7 then dt=dt-1;
    output;

    type='Thanksgiving Day';
    dt=intnx('week.5',fdo_nov,(weekday(fdo_nov) ne 5)+3);
    output;

    type='Christmas Day Observed';
    dt=MDY(12,25,YEAR);
    if weekday(dt)=1 then dt=dt+1;
    else if weekday(dt)=7 then dt=dt-1;
    output;
  end;
  keep dt type;
run;

proc sort data=holidays;
  by dt;
run;

/* Create a data set of weekends via the WEEKDAY function  */

data weekends;
  length type $25;
  type='Weekend';
  do dt=&start to &stop;
    if weekday(dt) in (1,7) then output;
  end;
run;

/* Create a data set of all the days in the specified date range. */
/* TYPE will have the value 'Workday' for all observations.       */
/* Specifying ALLDAYS first in the following MERGE will allow any */
/* date matches from from HOLIDAY or WEEKEND to overwrite the     */
/* value of TYPE with the appropriate type of day.                */

data alldays;
  length type $25;
  type='Workday';
  do dt=&start to &stop;
     output;
  end;
run;

data calendar;
  format dt date9.;
  merge alldays(in=a) weekends(in=w) holidays(in=h);
  by dt;
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

These results show the original start date and end date values, and the number of workdays between them.

Operating System and Release Information