topic Re: Date Increment in SAS Programming

Date Increment

vidyasagar1 — Wed, 17 Nov 2021 15:32:22 GMT

Hi, please help me to write a code for the below request.

For first two model Build_Date will be today's date and for next four model Build_date is incremented by 1, it has to follow same sequence for next 6 models. The Build_Date

should exclude weekends and the list of Holidays we provide.

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735
736
737
777
775
772
773
787
797
747
;
RUN;

Expected Output:

Model Build_Date

735 11/16/2021

736 11/16/2021

737 11/17/2021

777 11/18/2021

775 11/19/2021

772 11/22/2021

773 11/23/2021

787 11/23/2021

797 11/24/2021

747 11/25/2021

Re: Date Increment

lopezr — Wed, 17 Nov 2021 22:23:38 GMT

Assuming your pattern will keep repeating, something like this will work.

/* Input data */;
DATA INPUT;
    INFILE DATALINES;
    INPUT MODEL $10.;
    DATALINES;
735
736
737
777
775
772
773
787
797
747
;
RUN;

/* Define your work days (code from: https://communities.sas.com/t5/SAS-Programming/macro-to-exclude-week-end-days-and-company-holidays-from-the/td-p/356141) */
data wrkdays (keep=begin);
  format begin date9.;
  array holidays(6);
  do date = '16nov2021'd to '31dec2030'd ;  **UPDATE with your date range;
    if date eq intnx('year',date,0,'b') then do;
      call missing(of holidays(*));
      i=0;
    end;
    if date eq holiday('NEWYEAR', year(date)) or
       date eq holiday('USINDEPENDENCE',year(date)) or
       date eq holiday('THANKSGIVING', year(date)) or
       date eq holiday('CHRISTMAS', year(date)) or
       date eq holiday('MEMORIAL', year(date)) or
       date eq holiday('LABOR', year(date)) then do; **UPDATE with Holidays you want to exclude;
      i+1;
      holidays(i)=date;
    end;
    if not(date in holidays or weekday(date) in (1,7)) then do;
      season=1;
      begin=date;
      output;
    end;
  end;
run;

/* Generate final data */
options intervalds=(workdays=wrkdays); **define date interval for INTNX function;
data want (drop = counter);
    set input;
    
    counter + 1; **start a counter;
    if counter = 7 then counter = 1; **reset the counter every 7th row, pattern restarts here;
      
    retain Build_Date; 
    if _n_ = 1 then Build_Date = "16nov2021"d ; **first row should be 11/16/2021. Use today() if you want date of submit;
    else if counter ne 2 then Build_Date = intnx('workdays', Build_Date, 1); **repeats previous date on counter = 2 and adds 1 work day otherwise, excludes weekends;
    
    format Build_Date mmddyy10.;
run;

proc print data=want;
run;

The last row (model 747) here will have 11/26/2021 instead of 11/25/2021 which is Thanksgiving Day. You'll need to modify the wrkdays data set to include the Holidays you wish to exclude.

Re: Date Increment

mkeintz — Thu, 18 Nov 2021 01:32:39 GMT

Apparently the models in rows 2 and 8 will have the same date value as the immediately preceding row. But every row except those at 2 modulo 6 should have date incremented by 1, skipping holidays and weekends.

If you are using US holidays, you can use the HOLIDAY functions as shown by @lopezr . You can put those dates in a hash object. Then just keep incrementing the date (except for row 2) until the new date is a weekday and is not in the hash object:

data want (drop=_:);
  input model $10.;
  retain date ;
  format date weekdate22. ;
  if _n_=1 then do;
    *populate holidays hash object, keyed on DATE;
    declare hash holidays();
       holidays.definekey('date');
       holidays.definedata('date');
       holidays.definedone();
    do _year=2021 to 2022;
      do _holiday_name='USINDEPENDENCE','NEWYEAR','THANKSGIVING','CHRISTMAS','MEMORIAL','LABOR';
        date=holiday(_holiday_name,_year);
        holidays.add();
      end;
    end;

    *initialize date to precede first model (assume first model will be TODAY() ) **;
    date=today()-1;
  end;

  if mod(_n_,6)^=2 then do date=date+1 by 1 until (holidays.check()^=0  and weekday(date) in (2,3,4,5,6)) ; end;
datalines;
735
736
737
777
775
772
773
787
797
747
RUN;

Re: Date Increment

andreas_lds — Thu, 18 Nov 2021 12:31:25 GMT

Looks the like the problem you have posted some time ago: https://communities.sas.com/t5/SAS-Programming/Incrementing-the-dates/m-p/780750