BookmarkSubscribeRSS Feed
Kirito1
Quartz | Level 8

I was working on SAS and I need to calculate expected approved per day in order to achieve the target.

So what I did is below but I got stuck at one point. 

data have;
input target Approved;
datalines;
130 76
130 88
120 45
130 23
130 99
;

data DRR;
set have;
DRR = target - approved / No. of working days left;
run;

I want a logic for Number of working days left in a month from today.
For Example: Today is 19JAN2023.therefore, Number of working days left is 9.If, we exclude 
Hence, for the first observation DRR would be
DRR = 130-76/9 = 6.
Please, help.

Thanks to all the contributors to this query in advance.

3 REPLIES 3
CarmineVerrell
SAS Employee

Try adding this to your code . This will help figure out how many weekdays left available in the month.

 

format endofmonth date9.;
endofmonth=intnx("month",today(),0,"end");
wdleft=intck("weekdays",today(),endofmonth+1,"c");

ChrisHemedinger
Community Manager

A number of years ago I wrote an example of the NETWORKDAYS function in SAS (it exists in Excel). This function counts weekdays (as in the example by @CarmineVerrell) and also considers holidays (as supplied in a data set you specify).

 

/* setup some holiday data */
DATA WORK.usholidays;
    length
        HolidayDate             8
        Comment               $ 27;
    format
        HolidayDate           DATE9.
        Comment               $CHAR27.;
    infile datalines 
        dlm=',' 
        missover
        dsd;
    input
        HolidayDate           : mmddyy12.
        Comment               : $CHAR27.;
datalines;
1/1/2010,New Year's Day
1/18/2010,Birthday of Martin Luther King Jr.
5/31/2010,Memorial Day
7/4/2010,Independence Day
7/5/2010,Independence Day (obs)
9/6/2010,Labor Day
10/11/2010,Columbus Day
11/8/2010,Veterans Day
11/25/2010,Thanksgiving Day
12/25/2010,Christmas Day
1/1/2010,New Year's Day
1/17/2011,Birthday of Martin Luther King Jr.
5/30/2011,Memorial Day
7/4/2011,Independence Day
9/5/2011,Labor Day
10/10/2011,Columbus Day
11/11/2011,Veterans Day
11/24/2011,Thanksgiving Day
12/25/2011,Christmas Day
2/21/2012,Washington's Birthday
1/1/2012,New Year's Day
1/2/2012,New Year's Day
1/17/2012,Birthday of Martin Luther King Jr.
2/20/2012,Washington's Birthday
5/28/2012,Memorial Day
7/4/2012,Independence Day
9/3/2012,Labor Day
10/8/2012,Columbus Day
11/12/2012,Veterans Day
11/22/2012,Thanksgiving Day
12/25/2012,Christmas Day
; 
run;

/*
 * Mimic the NETWORKDAYS example here:
 *   https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7
 */
proc fcmp  outlib=work.myfuncs.dates;
  function networkdays(d1,d2,holidayDataset $,dateColumn $);

    /* make sure the start date < end date */
    start_date = min(d1,d2);
    end_date = max(d1,d2);

    /* read holiday data into array */
    /* array will resize as necessary */
    array holidays[1] / nosymbols;
    if (not missing(holidayDataset) and exist(holidayDataset)) then
        rc = read_array(holidayDataset, holidays, dateColumn);
    else put "NOTE: networkdays(): No Holiday data considered";

    /* INTCK computes transitions from one day to the next */
    /* To include the start date, if it is a weekday, then */
    /*  make the start date one day earlier.               */
    if (1 < weekday(start_date)< 7) then start_date = start_date-1;
    diff = intck('WEEKDAY', start_date, end_date);
    do i = 1 to dim(holidays);
      if (1 < weekday(holidays[i])< 7) and
         (start_date <= holidays[i] <= end_date) then
            diff = diff - 1;
    end;
    return(diff);
  endsub;
run; quit;

options cmplib=work.myfuncs;

/* test with one value */
data _null_;
  start_date = '31DEC2010'd;
  end_date = '31MAY2011'd;
  days = networkdays(start_date, end_date, "work.usholidays","holidaydate");
  put days=;
run;

/* test with data set of values */
data test;
  length dates 8;
  format dates date9.;
  infile datalines dsd;
  input dates : date9.;
  workdaysSince = networkdays(dates, today(), "work.usholidays","holidaydate");
datalines;
01NOV2010
21NOV2010
01DEC2010
01APR2011
;
title "As of &SYSDATE";
proc print data=test; run;

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ballardw
Super User

In general you want to clearly define what you want. Your "working day" may not be my working day. I know organizations that consider weekends working days but not Monday. My dentist doesn't work on Friday. At least one company I worked for that normally worked on Sundays didn't on Super Bowl Sunday, and another closed for the first week of deer hunting season.

 

An example for one date does not provide a general rule that could be applied to all months without such details.

 

Additionally, your pseudo-code is not using order of operations correctly.

data _null_;
  drr = 130 - 76/9;
  put drr=;
run;

shows Drr= 121.55555556 (approximately).

So your Drr calculation is drr = ( 130 - 76)/9;

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 448 views
  • 4 likes
  • 4 in conversation