Background

Let’s assume you have some dates in a custom format:

  date20131007 month102013

SAS Reports need to be able to a) present dates in a human readable format and b) understand dates to allow filtering and other funky stuff.

For that reason we need a way of translating these custom dates into SAS dates.

Step 1 – Get an Information Map with a date field

Use an existing one, or see http://support.sas.com/kb/35/471.html for more information on creating an Information Map.

Step 2 – Edit the Expression of your date field

1. Open the Properties for your date field.
2. Then on the Definition tab, click “Edit” in the “Expression Settings” section.

Step 3 – Magic

1. Change the Type to Date so that SAS can treat it as a date field from now on.
2. Then change the Expression Text to something like this:

input(substr(<<mytable.mydatefield>>,5,8), yymmdd8.)

wat

What’s happening here is that we’re translating the custom date string into a SAS date using what’s known as an INFORMAT.

SUBSTR (string, 5, 8) – Takes a substring from the given string, starting at character 5, with a length of 8 characters. In other words extracting the string date20131007 month102013

INPUT (string, yymmdd8.) – Takes a string and interprets it using the informat “yymmdd8.”. That informat is provided by default with SAS. What we’re doing here is saying to SAS “Here’s a string, but I want you to start treating it as a date. So that you know which part is the year, and which part is the month etc, use this informat as a guide”. Then SAS can know that dd = 07, mm = 10, and yy = 2013.

We’ve effectively translated a string in custom format into a SAS date.

My mind is blown. Now what?

Interpreting the dates as dates means we can now make it human-friendly in our reports, and also allows us to do some excellent SAS-native date filtering.

Formatting

What we specified earlier was an INFORMAT – in other words an interpretation format. What we can do, now that the date is stored as a SAS date, is specify an OUTPUT format, so that we can represent the date in a variety of ways in our reports.

1. Go back to the Properties dialog for your date field
2. On the Classifications tab is a “Formats” section. Change the “Format Type” to “Date/Time” and look at the available formats.

Selecting a format will take your date and represent it as a different string depending on the format you choose. Some examples:

Format	Output
DATE	07OCT13
DAY	7
WEEKDAY	1
MONNAME	October
DDMMYY	07/10/13
DOWNAME	Monday

Filtering

We can also now use SAS to filter dates in a very cool way. For example I can now filter all records which were created in 2013, or all records created after a certain date, or on a certain date, etc.

1. Create a new Filter and choose your date field as the Data Item.
2. Then set your Condition to “Year to date” – this will filter all your results to only show ones where the date falls between 1 Jan 2013 and today.
3. Click OK

A note on filtering

It’s always preferable to apply a filter at the Information Map level, rather than typing in a manual filter when you’re creating your Web Report. A filter on the Information Map will mean the data is filtered at the source, rather than decoding a bunch of information and only filtering once we get to the report level.*

* My understanding is that this is only with certain databases. Some allow optimisation by passing through filtering into the queries they make against the source databases. Still a good practice if you can do it.