BookmarkSubscribeRSS Feed
Flip
Fluorite | Level 6
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
8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Flip
Fluorite | Level 6
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Patrick
Opal | Level 21
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);
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Flip
Fluorite | Level 6
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Flip
Fluorite | Level 6
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 8 replies
  • 1460 views
  • 0 likes
  • 3 in conversation