topic Re: Fiscal quarter and year in ODS and Base Reporting

Fiscal quarter and year

Sanjay_M — Tue, 06 Sep 2011 12:52:34 GMT

How can I find out the UK fiscal quarter and year from SAS Date please?

Please note the UK financial year starts from 6th April - 5th April next year

i.e. 6-Apr-01 to 05-Jul-01 is the first quarter of year 2001.

Thanks.

Re: Fiscal quarter and year

data_null__ — Tue, 06 Sep 2011 15:25:37 GMT

I don't know if this what you're looking for but it may help you figure it out.

data test;
   input type:$1. date:date.;
   fy0 = intnx('year.4',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;

Re: Fiscal quarter and year

art297 — Tue, 06 Sep 2011 15:40:00 GMT

Thank you for forcing me to learn something new. I wasn't even aware that SAS had this capability built in, but I think that the following example does exactly what you are looking for:

options intervalds=(FiscalQuarter=FQDS);

data FQDS(keep=BEGIN SEASON);

start = '06APR2009'D;

stop = '05APR2015'D;

do BEGIN=start to stop;

season=0;

if day(begin) eq 6 then do;

if month(begin) eq 4 then season=1;

else if month(begin) eq 7 then season=2;

else if month(begin) eq 10 then season=3;

else if month(begin) eq 1 then season=4;

end;

if season gt 0 then output;

end;

format BEGIN DATE.;

run;

data test;

format dates date9.;

input dates date9.;

quarter=INTINDEX( 'fiscalquarter', dates );

cards;

14jan2011

5apr2011

6apr2011

5jul2011

6jul2011

14aug2011

;

Re: Fiscal quarter and year

Sanjay_M — Wed, 07 Sep 2011 08:15:00 GMT

Hello art297,

Thanks.

In your example in the first step you are running through a range of dates to find out the quarters first.

Does that mean that I have to do this for all the dates in my dataset?

I've managed to write this code. I "think" it is right unless you point out some flaw

data date_test; input dt1 ; format dt1 date9.; informat dt1 date9.; datalines;

"31Dec2001"d "01Jan2002"d "05Jan2002"d "06Jan2002"d "05Apr2002"d "06Apr2002"d "01May2002"d "05Jul2002"d "06Jul2002"d "01Aug2002"d "01Sep2002"d "05Oct2002"d "06Oct2002"d "01Nov2002"d "01Dec2002"d "31Dec2002"d "01Jan2003"d "05Jan2003"d "06Jan2003"d "06Feb2003"d "06Mar2003"d "05Apr2003"d "06Apr2003"d ;

run;

data a (keep=reportdate fiscal_qtr fiscal_year prevyear curryear a); set date_test; reportdate=dt1; format reportdate date9. q1_start_date date9. q1_end_date date9. q2_start_date date9. q2_end_date date9. q3_start_date date9. q3_end_date date9. q4_start_date date9. q4_end_date date9. ; if reportdate >= input('06APR'||put(year(reportdate), 4.), date9.) then do; prevyear = put(year(reportdate) , 4.); curryear = put(year(reportdate) + 1, 4.); end; else do; prevyear = put(year(reportdate) - 1, 4.); curryear = put(year(reportdate), 4.); end; if reportdate >= input('01JAN'||put(year(reportdate), 4.), date9.) and reportdate <= input('05APR'||put(year(reportdate), 4.), date9.) then fiscal_year=year(reportdate) - 1; else fiscal_year=year(reportdate); q1_start_date = input('06APR'||prevyear, date9.); q1_end_date = input('05JUL'||prevyear, date9.); q2_start_date = input('06JUL'||prevyear, date9.); q2_end_date = input('05OCT'||prevyear, date9.); q3_start_date = input('06OCT'||prevyear, date9.); q3_end_date = input('05JAN'||curryear, date9.); q4_start_date = input('06JAN'||curryear, date9.); q4_end_date = input('05APR'||curryear, date9.); if reportdate >= q1_start_date and reportdate <= q1_end_date then do; fiscal_qtr = 1; end; else if reportdate >= q2_start_date and reportdate <= q2_end_date then do; fiscal_qtr = 2; end; else if reportdate >= q3_start_date and reportdate <= q3_end_date then do; fiscal_qtr = 3; end; else if reportdate >= q4_start_date and reportdate <= q4_end_date then do; fiscal_qtr = 4; end;

run;

Is there any easier way please.

Re: Fiscal quarter and year

art297 — Wed, 07 Sep 2011 13:22:22 GMT

I may or may not understand what you are trying to do. In your code you try to KEEP a variable called "A", but no such variable is ever created.

Then you set two variables to the same values, namely prevyear and fiscal_year. Do you need both?

If you elect to use the method I suggested, based on what I THINK you are trying to do, you would only have to include the range of dates once, store the interval datasets in a catalog, and include the option in your config file. That process is described in the SAS documentation.

Creating the following two files would suffice for all years between 1800 and 3000:

options intervalds=(FiscalQuarter=FQDS

FiscalYear=FYDS);

data FQDS(keep=BEGIN SEASON);

start = '06APR1800'D;

stop = '05APR3000'D;

do BEGIN=start to stop;

season=0;

if day(begin) eq 6 then do;

if month(begin) eq 4 then season=1;

else if month(begin) eq 7 then season=2;

else if month(begin) eq 10 then season=3;

else if month(begin) eq 1 then season=4;

end;

if season gt 0 then output;

end;

format BEGIN DATE.;

run;

data FYDS(keep=BEGIN SEASON);

start = '06APR1800'D;

stop = '05APR3000'D;

do BEGIN=start to stop;

if day(begin) eq 6 and month(begin) eq 4 then do;

season=year(begin);

output;

end;

format BEGIN DATE.;

run;

Then, to achieve the same result as you did with your code, only the following would be needed:

data a;

set date_test (rename=(dt1=reportdate));

prevyearyear=INTINDEX( 'fiscalyear', reportdate );

curryear=INTINDEX( 'fiscalyear', reportdate )+1;

fiscal_year=INTINDEX( 'fiscalyear', reportdate );

fiscal_qtr=INTINDEX( 'fiscalquarter', reportdate );

run;

HTH,

Art

Re: Fiscal quarter and year

Sanjay_M — Wed, 07 Sep 2011 13:30:52 GMT

Thanks. I will store those dataset with all date values in a permanenet library. Good stuff.

Re: Fiscal quarter and year

miriamm — Fri, 11 Apr 2014 18:39:00 GMT

I know this is an old post but I found it in my efforts to do some state fiscal year stuff. I tried the above programs that were posted and I found them difficult and cumbersome. I ended up scrapping them and came up with the above. This would be for fiscal years that run July 1 to June 30 but could be easily adapted to the start and stop dates that you need. Just wanted to put this out there for other users.

data work.appointments (drop=appt_year appt_month);

set WORK.QUERY_FOR_APPOINTMENTS ;

format appt_date date9.;

appt_date = datepart(appointment_date);

appt_year = year(appt_date);

appt_month = month(appt_date);

if appt_month ge 7 then do;

sfy = appt_year+1;

if appt_month in (7,8,9) then qtr = 1;

else if appt_month in (10,11,12) then qtr = 2;

end;

else if appt_month le 6 then do;

sfy = appt_year;

if appt_month in (1,2,3) then qtr = 3;

else if appt_month in (4,5,6) then qtr = 4;

end;

run;

Re: Fiscal quarter and year

raqthesolid — Sun, 25 Sep 2016 18:42:41 GMT

This looks quite complicated for a beginner, I have two variables like the month (in numbers 1-12) and year (2004 -2015) and I want to define my fiscal year starting from July and ending in June. Kindly write some codes for me thanks. like for 2004 start from 07(month) 2004 (year) and ends in 06(month) 2005(year) and so on. thanks