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 » Coding & Data, Stored Processes

SAS Stored Process: How do I use a Date Range Prompt?

Submitted by on 2011-12-20 – 5:00 AM One Comment

After some not-so careful consideration, I decided to take the SAS BI Content Developer exam.  Some folks I know who have passed the exam recommended that I study how to work with SAS prompts and SAS Stored Processes.  So I am making up examples and working through some exercise drills of my own creation (oooh … scary).  I am noticing some of the features and nuisances of working with the Date Range prompts, which I am talking about today.

Working with SAS Prompts

When I started using stored processes several years ago, it was not quiet this easy to setup date prompts, so the prompts are a welcome improvement. In this example, a Proc Tabulate summarizes the sales by location and date.  The Date Range prompt allows the user to select a custom date range between Jan 1995 and December 1998.

This figure shows how the prompt looks and the results.

Check if the User is Insane …

I like this prompt because it does have some built in “sanity” checks. For instance, I tried to make the end date before the start date and it didn’t like it.  You can see it generated an error message, which use to be called Sanity checks, in the early Windows days.

This capture is a little dramatic with a date of June 1900. If using Jan 1996 to Jun 1995 – the same error message appeared.  These error messages help the user to not make mistakes that mess up how your stored process works.  This saves you a lot of programming time trying to trap this issue in the code.


Use the Automatic Labels in Your Output

This is a really cool prompt – I selected the Date Type as Month and notice that the six macro variables are automatically created.

  •  Two have the default values as the beginning of the month (MONYR_MIN and MONYR_MAX)
  •  Two have the month end dates (MONYR_MIN_END/MONYR_MAX_END)

I used four of the variables.   The labels being available is a nice feature, so those were placed in the TITLE statement so the user knows what was selected.  I know this screen capture is a little busy – I want you to see how the data and the prompt window work with the code.

  1. Data table:  You must use a date variable for the prompt to work.  In the code I used a BETWEEN operator.  Note that MONYR uses the MONYY format for the values – but all of the dates are stored as the first of the month (01JUN1995, 01JAN1995).  You have to know how the data looks so you can pick the correct macro variable.
  2. Prompt: Select the Date range prompt type and then use the generated macro variable as it makes sense in your code and data.    Do a lot of date testing to ensure the user gets the expected answer.  If this dates were (15JUNE1995, 31JUN1995) then using the MONYR_MAX=01JUN1995 would not return all of June’s sales.  Is that what the user expects?


Out of curiosity, I tried a Date range prompt using a data table that had days instead of summarized month to see what differences there were. Of course the main difference is that the user can select the days she wants to view. This is handy; however, this table only goes to June 4, 2011 and the user is selecting September 30, 2011. Since there is not any error checking in the code, the STP generates an empty report.

Workaround: If there is a limit to the table date value, then it makes sense to pre-set the default.  A table recreated daily may not have such a limit.

Another cool thing about this method is that the user can select relative values.  If you have the user selections  in the title then it assumes the user knows what she selected.  I can see this leading to issues.  As I noted above, the data does not go past June – so if the user selects the relative values – the title does not indicate the end dates.

Workaround: Add the actual date values or use those value alone.  Not very sexy in this screen capture.

My thought is if the user does a screen shot of the report and gives to her manager a few days later – the report is more clear with the date available.


Learn More about SAS Stored Processes and Prompts

You can learn more tips and tricks for creating, debugging, and using SAS stored processes in the 50 Keys to Learning SAS Stored Processes book. It's a complete guide to SAS stored processes. Check Amazon for best pricing and quick shipping!

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.


One Comment »

  • Belkis says:

    Extremely information! I have been seeking something such as this for a little bit now. Thanks!

1 Pingbacks »