BookmarkSubscribeRSS Feed
vidyasagar1
Obsidian | Level 7

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

3 REPLIES 3
lopezr
Obsidian | Level 7

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.

 

 

 

mkeintz
PROC Star

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
andreas_lds
Jade | Level 19

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

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!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 363 views
  • 0 likes
  • 4 in conversation