DATA Step, Macro, Functions and more

Fiscal Year

Reply
Super Contributor
Posts: 359

Fiscal Year

I was using intnx('year.10', dt, 1) to calculate our fiscal year starting in October. I was just told that our fiscal year actually starts on the first Sunday in October, not October first.

I am not seeing a way to do this with the INTNX function alone. Any ideas?

I think I figured it out intnx('year.10', intnx('week.1', dt,0),1)


Message was edited by: Flip
Super Contributor
Super Contributor
Posts: 3,174

Re: Fiscal Year

You will need to nest your two INTNX functions with the outer INTNX representing the "first Sunday" derivation logic (increment the date forward).

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 359

Re: Fiscal Year

If I read you correctly you are part right. I do need to change it to
intnx('year.10', intnx('week.1', dt,1),1) since Sunday is the first day, but the week.1 must be inner. Or maybe that is what you meant.
Super Contributor
Super Contributor
Posts: 3,174

Re: Fiscal Year

Okay - so have you actually tried your suggested code - I did and got a week-day of Thursday, shown below.

From what I understand, you want to advance the date to the next Sunday *AFTER* the first of a calendar month start-date.

Scott Barry
SBBWorks, Inc.

52 data _null_;
53 dt = mdy(9,23,2009);
54 x = intnx('year.10', intnx('week.1', dt,1),1);
55 format x dt date9.;
56 put x= dt= x= downame3.;
57 run;

x=01OCT2009 dt=23SEP2009 x=Thu
NOTE: DATA statement used (Total process time): Message was edited by: sbb
Respected Advisor
Posts: 4,173

Re: Fiscal Year

As Scott said: The OUTER intnx representing the first Sunday (and the inner October 1).

This one works:
x = intnx('week',intnx('year.10',dt,1),1);
Super Contributor
Super Contributor
Posts: 3,174

Re: Fiscal Year

However, I would expect a given date would already be within the fiscal year/month which would mean that the inner INTNX would decrement, not increment. Again, it would help if you ran the code for self-diagnosis and desk-checking - hopefully the sample code provided helps.

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 359

Re: Fiscal Year

I discovered the underlying problem. I was told the FY started the first Sunday in October, but they actually calculate it on a 5-4-4 calendar. This means a table lookup is the only option.
Luckily the date table they have is indexed so it works well.
Super Contributor
Super Contributor
Posts: 3,174

Re: Fiscal Year

I work with clients who have a fiscal-year application and we use a PROC FORMAT to derive the following:

- fiscal-year start date (maintained 5 years past and future for 3 years).
- fiscal-year "period" - format: yyyy/pp, where "pp" could be 01 to 13 - a character var.
- fiscal-year day-of-year (like Julian date but for the fiscal-year relevance).
- fiscal-year week-of-year (like ISO week of year).

You can create one format that returns each of the above values as a sub-field, parsed with the SCAN function.

Scott Barry
SBBWorks, Inc.

Pertinent reference:
http://en.wikipedia.org/wiki/Fiscal_year
Super Contributor
Posts: 359

Re: Fiscal Year

Here they have a macro that does current plau 2 back. My dates are bookings, so I need to go in the future.. I agree the format solution works well. I am using:

set mart.dt(keep = clndr_dt FY ) key = clndr_dt/unique;
if (not _iorc_%sysrc(_sok)) then do;
_error_ = 0;
end;

which finds the FY quickly.
Ask a Question
Discussion stats
  • 8 replies
  • 242 views
  • 0 likes
  • 3 in conversation