topic Re: Who to create a fiscal year starting in October in SAS Procedures

Who to create a fiscal year starting in October

coba — Mon, 11 Oct 2010 19:23:50 GMT

I searched in the forum but couldn't find the answer I am looking for.

I have a data set containing information per quarter, for example, (format date is date7.), How do I create FY?

type date qtr FY

A 01oct08 4 2008/2009
A 01jan09 1 2008/2009
A 01apr09 2 2008/2009
A 01jul09 3 2008/2009
A 01oct09 1 2009/2010
more lines .... and more columns

thank you very much for your help just to clarify I have not been able to create the field "FY" showing in the example, I would like to be able to group all my dates in this way

Message was edited by: coba

Re: Who to create a fiscal year starting in October

Paige — Mon, 11 Oct 2010 19:58:41 GMT

IF MONTH(DATE)>=10 then FY=YEAR(DATE)+1;

Re: Who to create a fiscal year starting in October

sbb — Mon, 11 Oct 2010 20:23:44 GMT

Better to consider setting up a SAS PROC FORMAT using instream data or with a SAS input file (using CNTLIN= parameter) and define the calendar start-date of each fiscal period along with the display information that represents that fiscal period. Here is an example:

PROC FORMAT;
VALUE FISCYRPD
'01OCT2008'D = '1 2008/2009'
'01NOV2008'D = '2 2008/2009'
'01DEC2008'D = '3 2008/2009'
OTHER = '?';
RUN;

If needed (likely so), the "formatted" character-string field above can be parsed into two separate variables for sorting/display purposes.

Or if the period always start on the 1st date, use INTNX to increment the date value up/back to the appropriate fiscal period-start-date and use a suitable output display FORMAT such as MMYY7. to display the resulting fiscal period-year.

Scott Barry
SBBWorks, Inc.

Re: Who to create a fiscal year starting in October

coba — Mon, 11 Oct 2010 20:58:36 GMT

The only thing I look against is that it would become a long list... as I have more than 25 years data per quarter!
Thank you very much for the display format.

Re: Who to create a fiscal year starting in October

sbb — Tue, 12 Oct 2010 00:56:14 GMT

You may be able to come up with a SAS derivation algorithm to generate the "year/month" start-date for each fiscal period, otherwise, yes, you are face with defining each period and then start-date to end-date range. Unless the "offset" is always some number of months forward/backward with a 1st of the month start-date, which can be derived programatically.

Scott Barry
SBBWorks, Inc.

Re: Who to create a fiscal year starting in October

coba — Tue, 12 Oct 2010 10:02:09 GMT

this is my test data:

type date
A 01-Oct-09
A 01-Jan-10
A 01-Apr-10
A 01-Jul-10
A 01-Oct-10
A 01-Jan-11
A 01-Apr-11
A 01-Jul-11

this is the test program

data work.fy1 (KEEP=TYPE DATE FY);
set work.fy;
format FY crpyrf.;
YEAR=YEAR(DATE) ;
QTR=QTR(DATE) ;
MTH=MONTH(DATE);
if qtr=4 then FY=year; else FY=year-1 ;
if qtr=4 then qtr=1; else qtr=qtr+1;
RUN;

this is the results:
type date FY
A 01-Oct-09 09-Oct
A 01-Jan-10 09-Oct
A 01-Apr-10 09-Oct
A 01-Jul-10 09-Oct
A 01-Oct-10 2010
A 01-Jan-11 2010
A 01-Apr-11 2010
A 01-Jul-11 2010

how do I manage to get this:
type date FY
A 01-Oct-09 09/10
A 01-Jan-10 09/10
A 01-Apr-10 09/10
A 01-Jul-10 09/10
A 01-Oct-10 10/11
A 01-Jan-11 10/11
A 01-Apr-11 10/11
A 01-Jul-11 10/11

thank you very much for your help

Re: Who to create a fiscal year starting in October

Patrick — Tue, 12 Oct 2010 11:36:39 GMT

Hi
Nothing that Scott didn't suggest already - only a fully working code example.
HTH
Patrick

data ctrl;
start='01oct1990'd;
retain fmtname 'FISCYRPD' type 'n';
do while (start lt '01oct2050'd);
end=intnx('month3',start,1,'b')-1;
label=put(start,date9.)||' '||cats(year(end+1)-1,'/',year(end+1));
output;
start=intnx('month3',start,1,'b');
end;

hlo='O';
label='** undefined FY **';
output;

run;

proc format library=work cntlin=ctrl;
run;

data _null_;
input type $ date anydtdte.;
put date= FISCYRPD.;
datalines;
A 01-Oct-09
A 01-Jan-10
A 01-Apr-10
A 01-Jul-10
A 01-Oct-10
A 01-Jan-11
A 01-Apr-11
A 01-Jul-11
;
run;

Re: Who to create a fiscal year starting in October

Sunny — Tue, 12 Oct 2010 11:44:31 GMT

data fy;
input type $ date :date9.;
format date date9.;
datalines;
A 01Oct09
A 01Jan10
A 01Apr10
A 01Jul10
A 01Oct10
A 01Jan11
A 01Apr11
A 01Jul11
A 01oct08
A 01jan09
A 01apr09
A 01jul09
A 01oct09
;
run;
data fy1;
set fy;
if MONTH(DATE) in (10, 11,12) then
do;
preyear = YEAR(DATE);
postyear = YEAR(DATE) + 1;
fiscal = compbl(preyear||"/"||postyear);
if qtr(date) = 4 then qtr = 1;
end;
else if MONTH(DATE) not in (10, 11,12) then
do;
preyear = YEAR(DATE) - 1;
postyear = YEAR(DATE);
fiscal = compbl(preyear||"/"||postyear);
qtr = qtr(date) + 1;
end;
drop P:;
RUN;

This way you can get what u r looking out... i m not sure whether you require qtr, but i have calculated it.

do let me know in case you require anything else.

Re: Who to create a fiscal year starting in October

coba — Tue, 12 Oct 2010 11:56:19 GMT

Thank you very much Sunny for your solution, specially now that my SAS skills did not allow me to get this solution.

Re: Who to create a fiscal year starting in October

Patrick — Tue, 12 Oct 2010 12:28:49 GMT

Hi

Sorry to insist here a bit but I believe that using a format is a more versatile approach.

Such FY categories tend to be needed over and over again for grouping and reports. That's why I think it's better to use a format which has to be created once instead of re-calculating everything over and over again.

You can store a format in a permanent catalogue - also in one which is always available when you start SAS.

Just to give you an idea of how to use a format:

/* create a format for FY */
data ctrl;
start='01oct1990'd;
retain fmtname 'FISCYRPD' type 'n';
do while (start lt '01oct2050'd);
end=intnx('month3',start,1,'b')-1;
label=put(start,date9.)||' '||cats(year(end+1)-1,'/',year(end+1));
output;
start=intnx('month3',start,1,'b');
end;

hlo='O';
label='** undefined FY **';
output;

run;

proc format library=work cntlin=ctrl;
run;

/* source data */
data have;
input type $ date anydtdte.;
datalines;
A 01-Oct-09
A 01-Jan-10
A 01-Apr-10
A 01-Jul-10
A 01-Oct-10
A 01-Jan-11
A 01-Apr-11
A 01-Jul-11
;
run;

/* print source using FY format */
title 'format applied on existing date var';
proc print data=have;
format date FISCYRPD.;
run;

/* add character var containing fiscal year string */
data want;
set have;
FY=put(date,FISCYRPD.);
run;

title 'additional var';
proc print data=want;
run;

HTH
Patrick

Re: Who to create a fiscal year starting in October

data_null__ — Tue, 12 Oct 2010 12:34:37 GMT

Consider using the SAS date functions. INTNX can find the start date of the fiscal year, using a shifted interval. Then fiscal year quarter can be determined with INTCK.

[pre]
data test;
input type:$1. date:date.;
fy0 = intnx('year.10',date,0,'B'); *start date of fiscal year;
length fy $9;
fy = catx('/',year(fy0),year(fy0)+1);
fyQT = intck('QTR',fy0,date)+1;
format date date. fy0 date.;
cards;
A 01oct08
A 01jan09
A 01apr09
A 01jul09
A 01oct09
B 10oct09
B 23jan10
B 14apr10
B 04jul10
B 16Oct10
;;;;
run;
proc print;
by type;
id type;
run;
[/pre]

To produce this which I think is what the OP wanted.

[pre]
type date fy0 fy fyQ

A 01OCT08 01OCT08 2008/2009 1
01JAN09 01OCT08 2008/2009 2
01APR09 01OCT08 2008/2009 3
01JUL09 01OCT08 2008/2009 4
01OCT09 01OCT09 2009/2010 1

B 10OCT09 01OCT09 2009/2010 1
23JAN10 01OCT09 2009/2010 2
14APR10 01OCT09 2009/2010 3
04JUL10 01OCT09 2009/2010 4
16OCT10 01OCT10 2010/2011 1[/pre]

Re: Who to create a fiscal year starting in October

coba — Tue, 12 Oct 2010 12:41:52 GMT

This is also an excellent solution.
Thank you for sharing your SAS skills with me and hopefully others will profit as well.

Re: Who to create a fiscal year starting in October

Sunny — Tue, 12 Oct 2010 17:38:57 GMT

Well.. see how many ways are there to solve a single problem. Thanks a lot for increasing my sas skills...