of the Month
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:
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:
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.
Updated August 6, 2009