topic Calculating Fiscal Year in SAS Programming

Calculating Fiscal Year

Ranjeeta — Mon, 10 Dec 2018 21:38:37 GMT

I am calculating the

I have a table with date, Month and Year values stored as numeric. I need to calculate fiscal years

e.g. April 01 2016 to March 31 2017 then FY 20617

I use the month and year functions to get the month and year columns How would I code to get fiscal years

Please advise

Thanks

Re: Calculating Fiscal Year

Kurt_Bremser — Mon, 10 Dec 2018 21:40:11 GMT

Please post usable example data in a data step with datalines; see my footnotes for hints.

Re: Calculating Fiscal Year

PGStats — Mon, 10 Dec 2018 22:04:33 GMT

Assuming you have DAY, MONTH and YEAR values, you can get the SAS date with

date = mdy(MONTH, DAY, YEAR);

You can then get the SAS date corresponding to the first day of your fiscal year with:

firstFYdate = intnx("YEAR.4", date, 0, "Beginning");

and the SAS date of the last fiscal year day as:

lastFYdate = intnx("YEAR.4", date, 0, "End");

The datastep statement:

FORMAT date firstFYdate lastFYdate yymmdd10.;

will give a readable format to these SAS dates.

Re: Calculating Fiscal Year

novinosrin — Mon, 10 Dec 2018 22:07:33 GMT

data fiscal_year;
do year=2000 to 2018;
do month=1 to 12;
   date=mdy(month,1,year);
   fedfiscalyear = year(date) + (month(date) gt 3);
	 output;
end;
end;
format date date9.;
run;

Re: Calculating Fiscal Year

Ranjeeta — Mon, 10 Dec 2018 22:09:38 GMT

data test;

input date MONTH YEAR

datalines;

17969 18554 18574

3 10 11

2009 2010 2010

;

I need to create a variable named FY e.g. 2009/10 would be if observation is in between April 01 2009 to March 31 2010

Thanksyou

Re: Calculating Fiscal Year

Kurt_Bremser — Mon, 10 Dec 2018 22:20:04 GMT

You misplaced your data; change columns to rows and vice versa, so you get correct data. Test your data steps before posting.

Re: Calculating Fiscal Year

Ranjeeta — Tue, 11 Dec 2018 15:37:22 GMT

Would you be able to advise how to write the if statemnt correctly I am getting an error as noted below:

data proceduresdate;
37 set procedures;
38 date=datepart(RemovalDate);
39 firstFYdate = intnx("YEAR.4", date, 0, "Beginning");
40 lastFYdate = intnx("YEAR.4", date, 0, "End");
41 FORMAT firstFYdate lastFYdate yymmdd10.;
42 FORMAT date Date9.;
43 if date between '01apr2009'd and '31mar2010'd then FY ='200910';
-------
388
202
ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

44 run;

Re: Calculating Fiscal Year

PGStats — Tue, 11 Dec 2018 18:33:33 GMT

BETWEEN is not recognized in this context. You must use

if '01apr2009'd <= date <= '31mar2010'd then FY ='200910';

Re: Calculating Fiscal Year

Ranjeeta — Tue, 11 Dec 2018 21:31:15 GMT

thankyou