Tips and tricks when working with dates in SAS Visual Analytics

[#1]

Working with date variables in SAS Visual Analytics can be both enjoyable and a bit frustrating. The challenge lies in the formatting of dates, especially given the variety of programming languages, format settings, file formats and databases. Consequently, date variables can be uploaded in numerous ways. Most of the time, the dates are displayed correctly. But occasionally, you might get unexpected results such as a normal character variable without a date format. This can be problematic when using some functions to calculate new items or trying to create visualizations like a Time series plot.

It might be preferable to format the date variable in SAS Studio before loading the dataset into Visual Analytics, especially when working with large datasets as some functions can take time to load. Nevertheless, its' good to know how to convert a variable to having a date format within Visual Analytics and in most cases, this will work just fine.

In this article, I will share some tips and tricks that I believe will be useful:

1. How to make date format
2. Calculate time between two dates
3. How to see dates in a specific period by using the Advanced filter
4. How to see dates in a specific period by using Advanced filter and a dynamic parameter
5. A summary of important functions when working with dates.

To follow the steps in this article, you will need a dataset that includes a character variable with dates and a measure variable.

1: How to make date format

If the date variable is uploaded into Visual Analytics without a date format, then it would appear like this:

But the goal is to achieve the following result:

Let’s go through all the steps needed to convert the variable to a date format:

Step 1: Our dataset contains a character variable called TransactionDate that appears as 01/01/08. At first glance it looks fine, but in reality it lacks a proper date format.

Step 2: The next step is to convert the TransactionDate by using the Parse function with a specified date format. So, go to the left pane, click Data and New data item, choose Calculated item, name the variable Date and write:

Parse(TransactionDate, 'MMDDYY32')

The new variable with date format would look like this:

Step 3: Sometimes, you may need to use only the Year, Month, Week or Quarter. To do this, then duplicate the Date variable, right-click the new variable to set the format to Year4 or other desired options and change the name of the variable to YearOnly:

Step 4: A dataset may not contain a full date, but instead have numeric variables for Day, Month, and Year. To calculate a date variable with the correct format, then use the DateFromMDY function : 

DateFromMDY('Month (COMMA12.)'n, 'Day (COMMA12.)'n, 'Year (COMMA12.)'n)

If the dataset only contains the year that you want to visualize in a Time series plot, then use the DateFromMDY function to set both the Day and Month to fixed values: 

DateFromMDY(1, 1, 'Year (COMMA12.)'n)

Note: To create a date variable which consists only of the Year, then change the format to Year4. 

Step 5: Let’s create some visualizations with our new calculations:

1. Drag a List table onto the canvas and include the variables Day, Month, Year, and Date.
2. On the right side of the page, drag in a Time series plot with the Time axis set to Date and the Measure set to ProductCostOfSale.
3. Below the first plot, drag in another Time series plot with the Time axis set to YearOnly, the Measure set to ProductCostOfSale and the Group set to ProductBrand.
4. Drag a Button bar control to the top of the page and set the Category role to YearOnly. This helps manage the visualization when there are too many dates.
5. Go to Actions in the right pane to link the List table and the Time series plot.
6. Choose the desired colors for the objects to enhance the visual appeal.

Step 6: The completed page will look like this:

2: Calculate time between two dates

At times, there is a need to calculate time between two dates, which could easily be done in Visual Analytics.

Step 1: In our dataset, there’s only one date variable. So let’s first create a new variable NowDate with today’s date using the Now function, which generates a datetime value from the current date and time. This approach is preferable to using a fixed date, as it updates the date each time the report is opened. Additionally, we use the Datepart function to convert a datetime value to a date value:

 DatePart(Now())

Change the format to DDMMYYYY, so it has the same format as the Date variable.

Step 2: To calculate the difference in days between two dates, use the TreatAS function on both date variables. Then, convert the dates to their numeric values which represent the number of days since January 1, 1960:

TreatAs(_Number_, NowDate)-TreatAs(_Number_, Date)

Ensure to set the desired aggregation for the new variable named DiffNowDate. In this case, choose the average.

This is how it’s done in Visual Analytics on Viya 3.5. However, in Viya 4, there’s no need to use the function TreatAS and the calculation can be done more easily:

NowDate-Date

Make sure to choose the correct format and aggregation. For example, use the COMMA2. format and set the aggregation to Average.

Step 3: Naturally, the calculation of the new variable DiffNowDate can be completed in a single step:

TreatAs(_Number_,DatePart(Now()))-TreatAs(_Number_,Date)

Step4: To visualize these new calculations, follow these steps:

1. Drag a List table onto the canvas.
2. Add the variables Date, NowDate and DiffNowDate. 
3. On the right side, add a Needle plot.
4. Set the X-axis to YearOnly and the Y-axis to DiffNowDate.

Step5: The result displays a Needle Plot showing the average number of days between today’s date and the different years:

3: How to see dates in a specific period by using the Advanced filter

Sometimes, you may want to visualize dates within a specific period. This can be achieved using the Advanced filter on a visualization. For example, to display dates in a Time series plot between the date today and 1 year back , follow these steps:

Step 1: First, create the new variable Date1yearAgo using the DateFromMDY function:  

 DateFromMDY(Month(DatePart(Now())),DayOfMonth(DatePart(Now())),(Year(DatePart(Now()))-1)) 

Step 2: For those who understood the previous example, you’ll notice that the same calculation can be performed using the TreatAs function:

TreatAs(_Date_, TreatAs(_Number_, NowDate)-366)

Note: I had to subtract 366 days to get the same result as in Step 1.

Again, if you are using Viya 4, then the calculation can be done like this: 

NowDate-366

This time, you need a date rather than a numeric value. Therefore, ensure to choose the correct format, which should be DDMMYYYY.

Step 3: Drag a Time series plot onto the canvas. Define the Time axis role to Date, and for example use ProductCostOfSale as the Measure variable.

Step 4: Select the Time series plot and go to the Filter option in the right pane. Click on New filter, choose Advanced filter and enter:

 Date > Date1yearAgo

Step 5: This can also be achieved directly in the Advanced filter without the need to create the variable Date1yearAgo: 

 Date > DateFromMDY(Month(DatePart(Now())),DayOfMonth(DatePart(Now())),(Year(DatePart(Now()))-1))

Or:

Date > TreatAs(_Date_, TreatAs(_Number_, NowDate)-366)

Step 6: This is what the result looks like:

Note: The Time series plot doesn't show any dates in 2024, since the dataset doesn't consist of any data in that year.

4: How to see dates in a specific period by using Advanced filter and a dynamic parameter

This example demonstrates how to see dates in a specific period, hence dates between a selected date plus 30 days ahead in a Time series plot. The following steps are required:

Step 1: First, create a Date parameter as follows:

Step 2: Let’s create a new date variable, Date+30Days, by using the TreatAs function to add 30 days to the Date parameter:

TreatAs(_Date_,TreatAs(_Number_, 'Date Parameter'p+30) )

On Viya 4, the TreatAs function is not necessary. Just ensure that the correct format is selected.

Step 3: To demonstrate the use of advanced filters and dynamic parameters, follow these steps to create the necessary visualizations:

1. Drag a Time Series Plot onto the canvas. Assign the Date variable to the Time Axis role and select a measure variable, such as ProductCostOfSale.
2. Add a Button Bar as a page control for the YearOnly variable to simplify date filtering in the next step.
3. Drag a Drop-Down List from the control objects onto the top of the canvas. Use Date as the category variable and define the correct date parameter. On Viya 4, go to Actions and set the Date parameter as the Parameter Link.

Step 4: Select the Time series plot, choose Advanced filter under the Filter section in the right pane and use the appropriate operators and functions to build the following:

IF (IsSet('Date Parameter'p))
    RETURN (BetweenExclusive(Date, 'Date Parameter'p, 'Date+30Days'n))
ELSE NotMissing(Date)

Step 5: Of course, the new date variable Date+30days can be omitted. Instead, the upper limit can be calculated directly in the Advanced filter: 

IF (IsSet('Date Parameter'p))
    RETURN (BetweenExclusive(Date, 'Date Parameter'p,TreatAs(_Date_, TreatAs(_Number_, 'Date Parameter'p)+30) ))
ELSE NotMissing(Date)

Step 6: The result appears as follows:

5. A summary of important functions when working with dates.

Before ending this article, I want to highlight some essential functions for working with dates:

• Year: Returns the year from a date value as a four-digit number.
• Month: Returns the month from a date value as a number in the range 1–12.
• Quarter: Returns the quarter from a date value as a number in the range 1–4.
• DayOfWeek: Returns the day of the week from a date value as a number in the range 1–7 (1 is Sunday).
• DayOfYear: Returns the day of the year from a date value as a number in the range 1–366.
• DayOfMonth: Returns the day of the month from a date value as a number in the range 1–31.
• WeekNumber: Returns the week of the year as a number in the range 0–53, where week 1 begins on the first Sunday of the year.
• DateFromMDY: Creates a date value from separate month, day, and year values.
• DatePart: Converts a datetime value to a date value.
• Now: Creates a datetime value from the current date and time.
• Parse: Interprets a numeric or datetime value from the input string. The format specifies the format used to interpret the string.
• TreatAs: Enables a numeric, date, or datetime value to be used as a different data type within other functions. The type specifies the desired data type.

I hope this article helps you when working with dates in Visual Analytics! 💙