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?
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?
> The format of my current set up is fiscal_year_ended DAMONYEAR
Is this a string or a SAS date?
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.
And format FYEAR with format year. .
@ChrisNZ wrote:
And format FYEAR with format year. .
Afraid not. There is a YEAR function applied to the intnx result.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.