Return to Archive

SAS Tip of the Month
August 2009

This month is a small tip on selecting data for a report, more specifically, a request for a report based on a particular month’s data. I had such a request last month and I thought I would pass on a little trick that can help solve such a question.

Let’s look at the following data:

   data sales;
      format date date9.;
      input date date9. value;
      cards;
   29JUN2009 500
   06JUL2009 490
   13JUL2009 480
   20JUL2009 510
   27JUL2009 520
   03AUG2009 530
   ;
   run;

Our request is to print out and give the total value of sales for July 2009. For the purposes of this example I am going to use a simple PROC PRINT call with a SUM statement to give the sum this data, but how about the selection of the records for the month of July?

One way to subset the data for the month of July is to use a WHERE statement in the following form:

   WHERE 01JUL2009<=date<=31JUL2009;

but, is there a solution where we do not have to worry about the number of days in the month? SAS does give one method, and that is by the use of the format MONYY, as the following WHERE statement demonstrates:

   WHERE PUT(date,monyy7.)="JUL2009";

Now putting this together, we have the following SAS code and output using the PRINT procedure:

   title1 "Listing of Sales for July 2009";
   proc print data=sales noobs;
      var date value;
      sum value;
      where put(date,monyy7.)="JUL2009";
   run;


   Listing of Sales for July 2009                 08:40 Thursday, August 6, 2009   1

              date    value

         06JUL2009      490
         13JUL2009      480
         20JUL2009      510
         27JUL2009      520
                      =====
                       2000

Using this trick in the WHERE statement allows us not to worry about the number of days in the month (are there 28 or 29 days in February this year?). Of course I could have also used a number of other formats including YYMM and MMYY to do the same -- refer to your local documentation for other formats that are available. Extending this, you could use a format like YYQ for calendar quarterly reports, or YEAR format for annual calendar year reports.

Hope you find this useful.