Let’s assume you have some dates in a custom format:
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
- Open the Properties for your date field.
- Then on the Definition tab, click “Edit” in the “Expression Settings” section.
Step 3 – Magic
- Change the Type to Date so that SAS can treat it as a date field from now on.
- Then change the Expression Text to something like this:
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.
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.
- Go back to the Properties dialog for your date field
- 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:
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.
- Create a new Filter and choose your date field as the Data Item.
- 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.
- 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.