BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools

Info Map: Use SAS Functions to get dates – oh yeah

Submitted by on 2011-10-18 – 2:20 AM One Comment

In Information Map Studio, you can use the Expression Editor and some SAS functions to create new data items from existing data items.  Here’s how to create some common Date data items.  SAS functions make it easy to perform actions and all you need is a variable and sometimes a parameter or two.  [Last week I talked about using functions to create a new data item.]

The following date variables are created using the Payment Date that is a full date value, for example  06Sep2008, 07Oct2008, and 11Oct2011.  It does not have a timestamp, which can make some functions perform differently.

Note: If your data source is a RDBMS table … you may not get the results you expect.  However, these tricks will work with SAS datasets.

Create the Year (YYYY) Variable

After creating a new data item, use the Edit button to start the Expression Editor window.   In the Expression Text area, use the Year function to extract the Year portion from the date.  In the following example, YEAR( data item) is used on the PaymentDate data item.  After running a test query, you can see the new variable result.

Hint: Always use the Validate button to check your expression.

make year

Create a Month-Year (MONYY)

This data value is created in a similar way but requires one extra step.   Use the INTNX function to set all the dates to the first day of the month.  In some applications, if you just apply a MONYY format, the data will not group properly.  I always take the extra step to make sure the data item meets my intention exactly.

For this example, the INTNX function requires the data item and 2 parameters.  The data item is Payment date.   The 2 parameters indicate the time period we want to use.  For this instance, we want MONTH and we want the current month so we use 0.

Hint: Use a 1 to increment the date by one month or a -1 to decrement the date by one month.

In the next step, use the Classifications, Aggregations, and Formats panel to to change the format to the MONYY, which looks like SEP08.

Create a Week (Wnn)

In this example,WEEK was substituted and a Week format was used to control the appearance.  There are three different Week formats.  Some base the week start date on Monday and others on Sunday.  The week format can be as long or short as you like.  This format does not include the Year.  When creating graphs where the user wants to see a trend over the week, this format can be a space saver.  Generally in that situation the user is less interested in the year or already knows the year.


Create a Quarter (YYn)

In this example, QUARTER was substituted and a Quarter format was used to control the appearance.  Quarter has many formats as well.

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , ,

One Comment »