Converting to fiscal years, using SAS

Often when analyzing data for annual reports, you need to summarize by fiscal year but people normally enter calendar dates. Also, if you didn’t know, now you know …

SAS and Excel use different date formats

If you have uploaded your data from an Excel file, you want to convert from an Excel date to a SAS date by subtracting 21, 916. This is because the reference date from which days are numbered in excel is January 1, 1900 and in SAS it is January 1, 1960. Coincidentally, 365.25*60 = 21,915 and at the century mark, we get shorted one day because 1900, 2000 etc. do not have a February 29. It’s true. Look it up. You can also read this article on SAS and Excel dates by Erik Tilanus. Or you can just quit being difficult and subtract 21,916.

Use four functions to find fiscal year

Annoyingly, my data were read in as character data, even though it was the number of days since January 1, 1900 which is definitely a number.

Function 1: Use input to convert data from character to numeric

The input function can be used to either convert data from character to numeric or vice versa. Simply include two parameters, the name of the variable you are reading in and the informat. Since I am creating a new variable, I may as well subtract the 21,916 from it at the same time.

Functions 2 and 3: Year and Month

If your fiscal year started in January, you wouldn’t be doing this, so clearly you need to get the month and year from your date. These are pretty obvious functions. Year gives you the year, and month gives you -wait for it – the month. Just give the name of the variable holding your date.

Function 4 : Use CATX to create a fiscal year variable

There is certainly more than one way to do this. If, for example, you wanted fiscal year 2020-21 to be referred to as 2020, you could just have something like:

if appmonth < 10 then fiscal_year = appyear ;
else fiscal_year = appyear + 1;

I can certainly see how you might want to do that if, for example, you were going to correlate year with some other variable and needed a numeric variable. In general, though, I find it a bit confusing when a date is January 24, 2022 and the year shows 2021.

The CATX function will concatenate any variables. The first parameter is what you want as a delimiter between the variables. You could use a space, but here I wanted a dash so that the result is something like 2021-2022.

For the current dataset, the fiscal year runs from October 1 to September 30 of the following year, so, if the month is less than 10, the beginning of the fiscal year was the prior year and the fiscal year ends in the year of the application date. If the month is 10 or greater, the beginning of the fiscal year is that year and the end of the fiscal year is the following year.

data test2 ;
set mydata.fixdata22 ;
app_date = input(application_date, 8.) – 21916 ;
appyear = year(app_date) ;
appmonth = month(app_date) ;
if appmonth < 10 then fiscal_year = catx(“-“, appyear -1, appyear) ;
else fiscal_year = catx(“-“, appyear, appyear +1);

So, that’s it. Now you have a new variable that allows you to subset and report on your data by fiscal year.

I wrote this post because my first thought of how to do this was embarrassingly complicated. I honestly don’t know what I was thinking. It involved multiple arrays and a Do-loop and then I gave it 30 seconds more thought and realized the solution was actually super easy.