24830 - Self- adjusting Date Formats

Sample 24830: Self- adjusting Date Formats

Often we want a table of information grouped by date ranges. For example, how many exams were completed last quarter and all earlier quarters combined? What was the number of machine tools produced last month, the previous month, and each of the previous years? How many whales were sighted this year, last year, and all the years before that since records were kept, grouped together?

In each of these examples, the recent date ranges are finer and more historic dates are clumped into larger ranges. When such reports are generated regularly, the dates in the finer ranges move back into the more clumped ranges.

This example demonstrates the creation of a dynamic self-adjusting format for the grouping so that without touching the code, the report will move ahead with new information and roll up earlier information into the larger clumped date ranges. Three SAS concepts, which relate to the technique, will be discussed: the INTNX function, nested formats, and the CNTLIN option in PROC FORMAT.

The Example
Let's say we want to group data as shown in the following table for run dates of October 1, 1999 and January 1, 2000. We will also want the code to work for April 1, 2000, and so on. The report shows information for the previous five quarters by quarter, and before that by calendar year.

The groupings will be developed from the most recent dates back to the earliest dates.

First, we must determine the beginning of each of the five previous quarters. If a portion of a year is left over, we must also determine that period. The remainder can be handled by grouping into calendar years. We could choose to eliminate data before some cutoff year, or we could choose to go back as far as we have data. The decision must be made depending on the situation, but if data are not eliminated from earlier time periods, the report obviously will grow longer -- perhaps causing unexpected pagination in the future. Two versions of the row labels for the report are shown.

Row labels on		Row labels on
report run on		report run on
Oct. 1, 1999		Jan. 1, 2000

1996		1996
1997		1997
Jan98 - Jun98		Jan98 - Sep98
Jul98 - Sep98		Oct98 - Dec98
Oct98 - Dec98		Jan99 - Mar99
Jan99 - Mar99		Apr99 - Jun99
Apr99 - Jun99		Jul99 - Sep99
Jul99 - Sep99		Oct99 - Dec99

The Method
First, we need to get the starting point and ending point for each of the previous five quarters. The INTNX function determines the first date of a time period based on a date interval, a reference date, and the number of such intervals. For example, February 1, 2000 is four month boundaries from October 29, 1999. (The month boundaries are November, December, January, and February.) Thus the lines

refdate = "29OCT1999"d;
wantdate = intnx("month",refdate, 4);

yield a wantdate equal to February 1, 2000. Similarly,

frstdate = intnx("year",refdate,-20);

assigns January 1, 1979 to frstdate.

We will put this function to work for our format, For:

start = intnx ("qtr", today(), 0 ) ;

we obtain the first date of the current quarter and we subtract 1 to get the last day of the previous quarter. Thus, we get the start and end dates for the previous five quarters in a do loop, and at the same time make the labels for our example:

do I = 1 to 5 ;
   end = start-1;
   start = intnx("qtr",end,0);
   label=put(start,monyy5.)
         ||"-"||put(end,monyy5.);
   output;
end;

Note that the format we are building cannot go in a format library because the TODAY function will get the current date from the operating system, and the format will be generated anew every time the report is run.

Next, we need to build the portion of the format to group dates previous to the five quarters obtained above, but in the same year as the fifth quarter back. This group may not exist, so we have an "if" test in the code:

if month(start) ne 1 then do;
   end = start - 1 ;
   start = intnx("year",end,0);
   label=put(start,monyy5.)
   ||"-"||put(end,monyy5.);
   output;
 end;

When the report is run on April 1, the part-year is not needed because the final start for the fifth quarter back is January 1. In this case, the "if" test fails, and this part-year group does not get created.

Finally, we need to group the dates previous to the five quarters, and previous to the part-year into years. This can be done using the SAS year format, nesting it in the format we are building. A nested format is bracketed in a VALUE statement:

	other = [year4.]

However, in a data set prepared for the CNTLIN option in PROC FORMAT, the brackets are omitted. The concept of "other" and the nesting are communicated to the FORMAT procedure through the variable HLO. This variable will have missing values for the rows corresponding to the five quarters and for the rows corresponding to the part-year, but for the row with "other" and the nested format, the value of HLO must be "OF." "O" is for "other" and the "F" is for the nested format. A good way to gain understanding of the variable HLO in PROC FORMAT is to create a few formats using things like low-0, 10-high, other = 'N', and a nested format, and then run:

   proc format cntlout = fmtout;
   run;

Examining FMTOUT can be quite enlightening, as you will find out about START, END, LABEL, HLO and other variables in it.

The entire code for the format for the example is:

data fmtdata(keep=fmtname start
                end label hlo) ;
 retain fmtname "QTRYR";
 start=intnx("qtr",today(), 0);
/* previous five quarters */
 do I = 1 to 5 ;
   end = start-1;
   start = intnx("qtr",end,0);
   label=put(start,monyy5.)
         ||"-"||put(end,monyy5.);
   output;
 end ;

/*Is part-year needed before whole years?*/ 
if month(start) ne 1 then do;
   end = start - 1 ;
   start = intnx("year",end,0);
   label=put(start,monyy5.)
   ||"-"||put(end,monyy5.);
   output;
 end;

/* Years */
hlo = "OF";
 label="year4.";
 output;
run;
proc format cntlin=fmtdata;
run;

This technical tip by Marianne Whitlock was presented at NESUG 99 in the Coder's Corner section. It is reprinted here with the author's permission.

Contact Information
Marianne Whitlock
1650 Research Boulevard
Rockville, MD 20850
301-251-4231
whitlom1@westat.com

If you have a Coder's Corner style presentation you would like to post on this site, please send it to techeditor@sas.com. If you have a SAS tip you'd like to share, send it to techeditor@sas.com.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Type:	Sample
Topic:	Non SAS Authors ==> Marianne Whitlock SAS Reference ==> Procedures ==> FORMAT SAS Reference ==> Formats

Date Modified:	2005-08-26 10:35:40
Date Created:	2004-10-14 07:51:07

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	Tru64 UNIX	n/a	n/a
		Solaris	n/a	n/a
		Linux	n/a	n/a
		OpenVMS Alpha	n/a	n/a
		HP-UX IPF	n/a	n/a
		HP-UX	n/a	n/a
		AIX	n/a	n/a
		ABI+ for Intel Architecture	n/a	n/a
		64-bit Enabled Solaris	n/a	n/a
		64-bit Enabled HP-UX	n/a	n/a
		64-bit Enabled AIX	n/a	n/a
		Windows	n/a	n/a
		Microsoft® Windows® for 64-Bit Itanium-based Systems	n/a	n/a
		OpenVMS VAX	n/a	n/a
		CMS	n/a	n/a
		z/OS	n/a	n/a

Support

Sample 24830: Self- adjusting Date Formats

Operating System and Release Information