Converting and splitting a date to a fiscal year

[krg1140]

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?

[Reeza]

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?

---

[ChrisNZ]

> The format of my current set up is fiscal_year_ended DAMONYEAR 

Is this a string or a SAS date?

[mkeintz]

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.

[ChrisNZ]

And format FYEAR with format year.  .

[mkeintz]

---

@ChrisNZ wrote:

And format FYEAR with format year.  .

---

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

[ChrisNZ]

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.