Calculating First and Last Days of the Current and Future Months in SAS Visual Analytics
If you came from a SAS programming background, you may have seen the INTNX function that applies basic arithmetic to dates. For example, you can use the function to add or subtract days, weeks, months, quarters, or years to an existing date. By setting the alignment parameter, you can establish if the resulting date will be in the beginning of the period, at the end, middle, or the same as the input date. You can still use this function in expressions in the Data Preparation component of SAS Visual Analytics, but unfortunately this function is not available in Visual Analytics Designer nor Visual Analytics Explorer. If all you need is to compute the first or last day of the current month, or next month, or any number of months in the future, there is a way you can derive that directly in Visual Analytics Designer or Explorer.
In order to do that, let’s start by looking at what it takes to calculate the first of the month given any date element. Then we will expand the idea and calculate the last of the given month. Finally, we will generalize the expressions so that you can get those calculations done for any number of months in the future.
First day of the month
The calculation of the first of the month is really straight forward. It’s accomplished by using the function DateFromMDY() like the expression in Figure 1 below.
Figure 1 - Expression for First of Month
The DateFromMDY() function has three parameters: month, day, and year, all numeric. For month, we simply use the Month() function, which returns the current month as a number for the given date. Similarly, for the third parameter we use the Year() function, which returns the current date’s year portion. The second parameter is fixed as 1, because we are aiming for the first of the month. The outer DateFromMDY() function reconstructs the date based on its components.
Last day of the month
For last of the month, things are slightly more complex than that, because the last day varies with the month and year, in the case of leap years. The trick is to calculate the first day of the next month and subtract one day.
Let’s start by calculating the first of next month. The next month can be simply obtained by adding 1 to the month number, first parameter in Figure 1. This works for all the months, except for December. We can account for that by using IF-THEN-ELSE logic, where every time the current month is 12 (December), we need to force the month to be 1 (January) and add 1 to the year, instead of just adding 1 to the month, like Figure 2 below:
Figure 2 - Expression for First of Next Month (option using IF-THEN-ELSE logic)
You can certainly use the expression in Figure 2 to obtain the first day of the next month, but this has some limitations that will become clearer at the end of this article, when we will be using parameters to calculate any number of months into the future. We will get back to this at the end and provide an example. For now, I’ll introduce a totally different expression that also calculates the first day of the next month without IF-THEN-ELSE logic. This expression (Figure 3) is a lot more flexible and concise:
Figure 3 - Expression for First of Next Month (preferred option)
The previous expression (Figure 3) introduces some new functions. Let’s take a look at them. The function Mod() returns the remainder from integer division. So the sub expression Month(Date) Mod 12 results in the given Date’s month number if it’s less than 12, or 0 if the month number is 12. If you add 1 to this value, you get the next month number, always. That doesn’t fix the year, though. For year, you can use the function Trunc() that returns the integer part of the parameter. The argument to function Trunc() is Month(Date) / 12, which is always less than 1, except for month of December. So the function Trunc() in Figure 3 returns 1 when the month is December (and that means advancing to the next year), otherwise it returns 0.
Now that you know how to calculate the first day of the next month, let’s subtract one day from that expression and obtain the last day of the month. In order to subtract 1 from a date, you must use the TreatAs() function to interpret the date as a number first, then transform the resulting number back to a date, using the same TreatAs() function, as shown in Figure 4:
Figure 4 - Expression for Last of Month (option leveraging First of Next Month)
The expression in Figure 4 leverages the calculated item First of Next Month that you have just seen in Figure 3, but if you prefer, you can combine them together in one single expression:
Figure 5 - Expression for Last of Month (single expression option)
Now you can parameterize the expressions you have built, so they can return the first or the last day of the current month or any number of months in the future.
Generic expressions for first and last days of current or any month in the future
Supposing that N Month is the name of the numeric parameter that informs the number of months to be added to the given date, where N Month >= 0, you can easily modify the previous expressions by adding the highlighted elements in Figuer 6:
Figure 6 - Expression for First of Month+N
In the expression above (Figure 6), if N Month is 1, all the highlighted elements cancel out and the final expression is equivalent to the one in Figure 3 (first on next month), which makes sense. Similarly, in the expression of Figure 7 below, if N Month is 0 the final expression is the same used in Figure 5 to calculate the last of the given month:
Figure 7 - Expression for Last of Month+N
If you like the option that uses IF-THEN-ELSE logic to calculate the first of the next month (Figure 2), the parameterized expression equivalent to it is found below (Figure 8). As said before, it has a limitation that it only works for parameter values between 0 and 11. In order to make it valid for N Month values greater than 11 you would need to modify the expression and add more logic, which would make the expression more and more complex and difficult to manage.
Figure 8 - Expression for First of Month+N (option using IF-THEN-ELSE logic)
If you understood the steps involved to derive the various expressions so far, the expression for parameterized last day of the month that is based on the expression in Figure 8 above should be straight forward, and I’ll leave it as an exercise for those that prefer the IF-THEN-ELSE method.
Sure. With a little bit of math you can adapt the formula from Figure 6 to account for negative number of months. For example, if you want to allow for subracting up to 1200 months (=100 years), the new formula would become like this:
If 100 is not enough, insead of 1200 you could use add 12000 and then subtract 1000 in the formula above and your new formula would be good for 1000 years!
The main point is that as long as you add a multiple of 12 and then subtract the equivalent number of years, the formula works as a charm.
Best,
Renato
