topic Re: Fiscal Year in SAS Programming

Fiscal Year

Flip — Fri, 08 Jan 2010 16:45:06 GMT

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

Re: Fiscal Year

sbb — Fri, 08 Jan 2010 18:52:43 GMT

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.

Re: Fiscal Year

Flip — Fri, 08 Jan 2010 19:28:31 GMT

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.

Re: Fiscal Year

sbb — Fri, 08 Jan 2010 20:51:45 GMT

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

Re: Fiscal Year

Patrick — Fri, 08 Jan 2010 22:34:16 GMT

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);

Re: Fiscal Year

sbb — Sat, 09 Jan 2010 02:40:39 GMT

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.

Re: Fiscal Year

Flip — Mon, 11 Jan 2010 14:17:25 GMT

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.

Re: Fiscal Year

sbb — Mon, 11 Jan 2010 14:28:42 GMT

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

Re: Fiscal Year

Flip — Mon, 11 Jan 2010 14:38:15 GMT

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.