topic Re: create trading days with sas function intnx in SAS Programming

create trading days with sas function intnx

Amelia6 — Mon, 06 Jan 2025 22:54:01 GMT

I'd like to create a date variable that is based on the number of trading days from an event date. For instance, the event date is 1/2/2024, I'd like to create a date for 100 trading days from 1/5/2025. Roughly speaking, trading days are week days plus the following holidays:

Third Monday of January — Martin Luther King Jr. Day
Third Monday of February — Presidents' Day
Last Monday of May — Memorial Day
June 19 — Juneteenth
July 4 — Independence Day
First Monday of September — Labor Day
Fourth Thursday of November— Thanksgiving Day
Dec. 25 — Christmas Day

I understand I can do newdate=intnx('weekdays',olddate,100) to count only week days. But how can I also account for(exclude) the above holidays? Thank you! Attached is sample of data with old dates that I'd like to calculate the new dates on. Thank you!

Re: create trading days with sas function intnx

whymath — Tue, 07 Jan 2025 02:37:59 GMT

You can just count:

data want;
  set raw.dates;

  _count_=0;
  do _n_=1 to 200 until(_count_=100);
    newdate=olddate+_n_;
    _year_=year(newdate);

    if not(
       weekday(newdate) in (1 7) /*Saturday,Sunday*/
       or newdate=holiday('mlk',_year_)
       or newdate=holiday('uspresidents',_year_)
       or newdate=holiday('memorial',_year_)
       or newdate=holiday('juneteenth',_year_)
       or newdate=holiday('usindependence',_year_)
       or newdate=holiday('labor',_year_)
       or newdate=holiday('thanksgiving',_year_)
       or newdate=holiday('christmas',_year_)
    ) then _count_+1;
  end;

  put olddate=  newdate= yymmdd10.;
run;

Re: create trading days with sas function intnx

Ksharp — Tue, 07 Jan 2025 02:50:04 GMT

libname x v9 'c:\temp';
data want;
 set x.dates;
 array x{8};
 count=0;
 do newdate=olddate to '1jan2050'd;
/*  x1=holiday('MLK', year(newdate));*/
  x1=nwkdom(3,2,1, year(newdate));   /*Third Monday of January — Martin Luther King Jr. Day*/
  x2=nwkdom(3,2,2, year(newdate));  /*Third Monday of February — Presidents' Day*/
  x3=nwkdom(5,2,5, year(newdate));  /*Last Monday of May — Memorial Day*/
  x4=mdy(6,19, year(newdate));  /*June 19 — Juneteenth*/
  x5=mdy(7,4, year(newdate));  /*July 4 — Independence Day*/
  x6=nwkdom(1,2,9, year(newdate));  /*First Monday of September — Labor Day*/
  x7=nwkdom(4,5,11, year(newdate));  /*Fourth Thursday of November— Thanksgiving Day*/
  x8=mdy(12,25, year(newdate));  /*Dec. 25 — Christmas Day*/


  if weekday(newdate) not in (1 7) and newdate not in x then count+1;
  if count=100 then leave;
 end;
 format newdate x: yymmdd10.;
 drop count;
run;

Re: create trading days with sas function intnx

Amelia6 — Tue, 07 Jan 2025 03:59:24 GMT

Thank you! It works beautifully!

Re: create trading days with sas function intnx

Amelia6 — Tue, 07 Jan 2025 04:00:13 GMT

Thank you! It works wonders!