topic Re: Converting and splitting a date to a fiscal year in SAS Programming

Converting and splitting a date to a fiscal year

krg1140 — Mon, 08 Mar 2021 23:39:30 GMT

Hi everyone,

I have a task which I need to "Create a new variable, FYEAR. If the month the fiscal year ended was June through December then FYEAR will equal the year in the FISCAL_YEAR_ENDED date. If the month the fiscal year ended was January through May, then FYEAR will equal the year in the FISCAL_YEAR_ENDED date less one."

The format of my current set up is fiscal_year_ended DAMONYEAR 2 digits for the date, 3 for the month and 4 for the year. For example today (March 8th 2021) would show up as 08MAR2021.

How can I instruct SAS to decipher the fiscal years? I first started by splitting the date such as

data mergedata1; set mergedata;
year=year(fiscal_year_ended);
month=month(fiscal_year_ended);
day=day(fiscal_year_ended);
run;

but now I'm looking to tell SAS whether the fiscal year and fiscal year ended should be the same?

Re: Converting and splitting a date to a fiscal year

Reeza — Mon, 08 Mar 2021 23:45:51 GMT

FYEAR = year(fiscal_year_ended) - 1*(month(fiscal_year_ended) < 6);

(month(fiscal_year_ended) < 6) will evaluate to a 0 or 1. If it's less than 6 it evaluates to 1, which becomes year(fiscal_year_ended) - 1* 1 = year-1
I'll leave the math for the 0 to you.

@krg1140 wrote:

Hi everyone,

I have a task which I need to "Create a new variable, FYEAR. If the month the fiscal year ended was June through December then FYEAR will equal the year in the FISCAL_YEAR_ENDED date. If the month the fiscal year ended was January through May, then FYEAR will equal the year in the FISCAL_YEAR_ENDED date less one."

The format of my current set up is fiscal_year_ended DAMONYEAR 2 digits for the date, 3 for the month and 4 for the year. For example today (March 8th 2021) would show up as 08MAR2021.

How can I instruct SAS to decipher the fiscal years? I first started by splitting the date such as

data mergedata1; set mergedata;
year=year(fiscal_year_ended);
month=month(fiscal_year_ended);
day=day(fiscal_year_ended);
run;

but now I'm looking to tell SAS whether the fiscal year and fiscal year ended should be the same?

Re: Converting and splitting a date to a fiscal year

ChrisNZ — Mon, 08 Mar 2021 23:47:32 GMT

> The format of my current set up is fiscal_year_ended DAMONYEAR

Is this a string or a SAS date?

Re: Converting and splitting a date to a fiscal year

mkeintz — Tue, 09 Mar 2021 00:16:21 GMT

If you are using the fiscal year convention in the US (fiscal year xxxx runs from June xxxx through May xxxx+1), then

   fyear=year(intnx('year.6',fy_enddate,0,'beg'));

The INTNX function "advances" the date value in FY_ENDDATE by 0 years, and aligns the result to the date beginning that year. But I am using "year.6" as the interval, not "year". "Year.6" identifies year intervals that begin in June.

Run the program below to see a 12-month series assigning fiscal years as you require:

data _null_;
 fy_enddate='31jan2019'd;
 do until (fy_enddate>'31dec2019'd);
   fyear=year(intnx('year.6',fy_enddate,0,'beg'));
   put fy_enddate=date9. fyear=;
   fy_enddate=intnx('month',fy_enddate,1,'end');
  end;
run;

No if test or other conditional programming statement required - just a function.

Re: Converting and splitting a date to a fiscal year

ChrisNZ — Tue, 09 Mar 2021 00:21:03 GMT

And format FYEAR with format year. .

Re: Converting and splitting a date to a fiscal year

mkeintz — Tue, 09 Mar 2021 00:22:19 GMT

@ChrisNZ wrote:

And format FYEAR with format year. .

Afraid not. There is a YEAR function applied to the intnx result.

Re: Converting and splitting a date to a fiscal year

ChrisNZ — Tue, 09 Mar 2021 00:26:45 GMT

Fair enough. I saw that you had provided the solution I had in mind but didn't see you'd decided to extract the year. All good.