As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Difference between revisions of "Macro Loops with Dates"

From sasCommunity
Jump to: navigation, search
m (References to Generating_Holiday_Lists with nwkdom examples)
(References for working with Time)
 
(13 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
Writing Macro Loops with Dates from Then to Now
 
Writing Macro Loops with Dates from Then to Now
 +
 +
Author/editor: [[User:Ron.Fehd.macro.maven|Ronald_J._Fehd macro maven]]
 +
 +
This paper has been published in SGF2013.QT: Quick Tips, formerly known as Coders Corner.
 +
 +
http://support.sas.com/resources/papers/proceedings13/343-2013.pdf
 +
 +
Audience: macro programmers
  
 
== Overview ==
 
== Overview ==
  
== Program ==
+
This page contains the programs, macros, tests, and calling programs
 +
in the paper
 +
 
 +
== Theory ==
 +
 
 +
'''Overview'''
 +
 
 +
* Theory
 +
** macro language produces text
 +
** macro loops require numbers
 +
** a date is a reference to a number, which is an index to an associative array
 +
** given a number, a macro returns text from the date associative array
 +
* Practice: macro as Black Box
 +
** macro DateLoop need numbers for its loop: start, stop and step
 +
** it calculates Begin and End of a date interval
 +
** and calls a macro subroutine
 +
*** -- either Date-Report-Zero or Year-Month --
 +
*** with parameters of Begin and End
 +
* macro subroutine can do either of
 +
** Small Data: Year-Month to return list of smaller-intervaled data sets
 +
** Big Data: Date-Report-Zero which is a Periodic Snapshot, a subset of Big Data with dates between beginning and end of a time interval
 +
 
 +
=== Programs showing Bytes of Theory ===
 +
 
 +
==== Program fips-codes ====
 +
 
 +
This program shows that the associative array of two-letter state codes
 +
has an index: the (U.S.) Federal Information Processing Standard (FIPS) number.
  
 
<source lang="sas">
 
<source lang="sas">
  /*    name: date-mvars
+
  /*    name: fips-codes
description: provide list of system mvars with dates
+
description: demo of associative array
purpose    : show how to convert dates to integers
+
purpose    : for slide
 +
*******/
 +
*system functions;
 +
data work.State_Info;
 +
attrib fips  length = 4
 +
      St    length = $2
 +
      State length = $22
 +
      ;
 +
do fips  = 1 to 79;
 +
  St    = fipstate(fips);
 +
  State = fipnamel(fips);
 +
  if St ne '--' then output;
 +
  end;
 +
stop;
 +
 
 +
PROC Print data = &Syslast;
 +
</source>
 +
 
 +
=== A Date is a Reference to an Integer ===
 +
 
 +
Use any of these macro assignment statements
 +
to get an integer of a date
 +
for use with macro loops.
 +
 
 +
<source lang="sas">
 +
*integers;
 +
%let today = 0;
 +
*let today = 366;
 +
*functions;
 +
*let today = %sysfunc(today());
 +
%let today = %sysfunc(mdy(04,1,2013));
 +
*date literal;
 +
*let today = %sysevalf('30Apr2013'd);
 +
*let today = %sysevalf('14Mar2013'd);
 +
*let today = %sysevalf('01May2013'd);
 +
 
 +
*display: note other formats;
 +
%put today: &today %sysfunc(putn(&today,weekdate29.));
 +
</source>
 +
 
 +
==== Program Info-in-Dates ====
 +
 
 +
This program shows over a dozen items of information
 +
available from the date associative array.
 +
 
 +
<source lang="sas">
 +
/*    name: info-in-dates.sas
 +
description: provide associative array of values from dates
 +
purpose    : show how to convert dates to N and name
 +
            for ODS date-stamped filenames
 
sas.help: About SAS Date, Time, and Datetime Values
 
sas.help: About SAS Date, Time, and Datetime Values
 
           Dictionary of Functions and CALL Routines
 
           Dictionary of Functions and CALL Routines
           Working with Dates in the SAS System: Comparing Durations and SAS Date Values
+
           Working with Dates in the SAS System:
          Working with Dates in the SAS System: Understanding How SAS Handles Dates
+
            Comparing Durations and SAS Date Values
 
+
            Understanding How SAS Handles Dates
other formats with year2:
+
reference:
%put today: &today %sysfunc(putn(&today,date7.));
+
  http://www.sascommunity.org/wiki/Date_datetime_time_stamp
%put today: &today %sysfunc(putn(&today,mmddyy10.));
+
 
*******/
 
*******/
%put sysdate: &Sysdate.;
+
%let today = 0;
%put sysdate9: &Sysdate9.;
+
%let today = 366;
 +
*let today = %sysfunc(today());
 +
*let today = %sysevalf('30Apr2013'd);
 +
%let today = %sysfunc(mdy(04,01,2013));
 +
options nosource;
 +
%put today: &today %sysfunc(putn(&today,weekdate29.));
 +
 
 +
%put day/month.N  :%sysfunc(day(&today));
 +
%put day/month.z2 :%sysfunc(putn(%sysfunc(day(&today)),z2));
 +
%put day/week.N  :%sysfunc(weekday(&today));
 +
%put day/week.name:%sysfunc(putn(&today,downame));
 +
%put day/year    :%substr(%sysfunc(juldate7(&today)),5,3);
 +
 
 +
%put month.N    :%sysfunc(month(&today));
 +
%put month.z2  :%sysfunc(putn(%sysfunc(month(&today)),z2));
 +
%put month.name :%sysfunc(putn(&today,monname));
 +
%put month.name3:%sysfunc(putn(&today,monname3));
 +
%put year      :%sysfunc(year(&today));
 +
 
 +
%put week/year.u:%sysfunc(week(&today,u));
 +
%put week/year.v:%sysfunc(week(&today,v));
 +
%put week/year.w:%sysfunc(week(&today,w));
 +
</source>
 +
 
 +
log:
 +
<pre>
 +
17        options nosource;
 +
today: 366      Sunday, January 1, 1961
 +
day/month.N  : 1
 +
day/month.z2 : 01
 +
day/week.N  : 1
 +
day/week.name:    Sunday
 +
day/year    : 001
 +
month.N    : 1
 +
month.z2  : 01
 +
month.name :  January
 +
month.name3: Jan
 +
year      : 1961
 +
week/year.u: 1
 +
week/year.v: 52
 +
week/year.w: 0
 +
</pre>
 +
 
 +
see also:
 +
 
 +
* [[Date_datetime_time_stamp]]
 +
 
 +
Note: these functions can be use in ODS filenames.
 +
 
 +
=== Syntax of Interval-Indexing function intnx ===
 +
 
 +
The Interval-Indexing function intnx
 +
saves the headaches of off-by-one errors
 +
when calculating previous begin- and end-dates of intervals.
 +
 
 +
Notes:
 +
* interval
 +
** a simple interval is week == 7 days
 +
** a complex interval is week2 == week*2 = 14 days
 +
** for fiscal year use qtr.N
 +
* date must be a number, not a reference like a date literal
 +
* increment:
 +
** negative means previous interval
 +
** zero means this time-interval, e.g.: week
 +
** positive means future
 +
* alignment
 +
** mid-month == month.15
 +
** sameday: e.g.: day.2 of last week or last.month.21st
 +
 
 +
<pre>
 +
intnx(interval  = (simple : day, week, month, ...)
 +
                  complex: week2, week.2 qtr.2)
 +
    ,start-from= date as integer
 +
    ,increment = (-12, -1, 0)
 +
    ,alignment = (begin, middle, end, same)
 +
</pre>
 +
 
 +
<source lang="sas">
 +
*name: demo-mvar-basics.sas;
 +
 
 +
* assignment;
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
%let interval = week;
 +
%let format = weekdate29.;
  
%let today = %sysevalf('10JAN2013'd);
+
* calculations;
%let today = %sysevalf('10Feb2013'd);
+
%let D_Begin  =%sysfunc(intnx(&Interval,&today,0,begin));
%let today = %sysevalf('21Dec2012'd);
+
%let D_Middle =%sysfunc(intnx(&Interval,&today,0,middle));
*let today = %sysevalf("&Sysdate9."d);
+
%let D_End    =%sysfunc(intnx(&Interval,&today,0,end  ));
*let today = %sysfunc(today()) ;
+
*let today = %sysfunc(date()) ;
+
  
%put today: &today %sysfunc(putn(&today,date9.));
+
* display;
%put today: &today %sysfunc(putn(&today,mmddyy10.));
+
 
%put today: &today %sysfunc(putn(&today,worddate18.));
 
%put today: &today %sysfunc(putn(&today,worddate18.));
%put today: &today %sysfunc(putn(&today,weekdate29.));
+
%put today: &today %sysfunc(putn(&today,&format));
 +
%put D_Begin : &D_Begin %sysfunc(putn(&D_Begin ,&format));
 +
%put D_Middle: &D_Begin %sysfunc(putn(&D_Middle,&format));
 +
%put D_End  : &D_End  %sysfunc(putn(&D_End  ,&format));
 +
</source>
  
*parts:;
 
%put day/month: &today %sysfunc(day(&today));
 
%put month: &today %sysfunc(month(&today));
 
%put year: &today %sysfunc(year(&today));
 
  
%put week/year: &today %sysfunc(week(&today,u));
+
== Programs ==
%put week/year: &today %sysfunc(week(&today,v));
+
%put week/year: &today %sysfunc(week(&today,w));
+
  
%put day/year: &today %substr(%sysfunc(juldate7(&today)),5,3);
+
=== Program Test-Data ===
 +
 
 +
<source lang="sas">
 +
/*    name: test-data
 +
description: provide data with dates
 +
purpose    : for use by other demo programs
 +
usage:
 +
%daterpt0(data = Library.TestData
 +
        ,var  = date
 +
        ,...);
 +
        /*******/
 +
DATA Library.TestData;
 +
    attrib EntityId length = 4
 +
            Date    length = 8 format = weekdate17.
 +
            Fact    length = 8;
 +
do Date = 0 to '01May2013'd;
 +
  do EntityId = int(Date/17);
 +
      do Value = int(Date**3/19);
 +
        output;
 +
        end;
 +
      end;
 +
  end;
 +
stop;
 +
run;
 +
</source>
 +
 
 +
== Macros ==
 +
 
 +
# Save these two macros in a folder 'C:\SAS-site\macros'
 +
# add this code to your autoexec:
 +
 
 +
<source lang="sas">
 +
Filename Project  '.';
 +
Libname  Library  '..\sas7b';
 +
* macros: autocall: *.sas;
 +
options  MautoSource
 +
        SASautos = (Project 'C:\SAS-site\macros' SASautos);
 +
</source>
 +
 
 +
The above code is from:
 +
[[Setting_Up_Project_Config_and_AutoExec]]
 +
 
 +
=== Macro DateLoop ===
 +
==== Macro DateLoop ====
 +
 
 +
note: This is New and Improved, different from the macro in the paper.
 +
 
 +
<source lang="sas">
 +
/*    name: ...\SAS-site\macros\dateloop.sas
 +
    author: Ronald J. Fehd  2013-March-28
 +
description: macro function do loop from loop-start to loop-stop
 +
purpose: standardize calling of report macros for many intervals
 +
NOTES: called macro must have parameters Begin and End
 +
sas.help: Incrementing Dates and Times
 +
            by Using Multipliers and by Shifting Intervals
 +
sas.wiki: http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
 +
    http://www.sascommunity.org/wiki/Do_which_until_or_while
 +
predecessor: http://www.sascommunity.org/wiki/Macro_CallMacr
 +
usage: demo
 +
%let today    = %sysfunc(today());
 +
%let interval = week;
 +
%let Begin    = %sysfunc(intnx(&interval,&today,-12,begin));
 +
%let End      = %sysfunc(intnx(&interval,&today,- 1,end  ));
 +
* testing;
 +
%dateloop(start    = &begin
 +
        ,stop    = &end
 +
        ,interval = day
 +
        )
 +
* small data: concatenate monthly data sets for previous-12 report;
 +
%put
 +
%dateloop(start    = &begin
 +
        ,stop    = &end
 +
        ,interval = month
 +
        ,MacroText= %nrstr(work.Year_Month_&year._&mm._&month)
 +
        );
 +
* big data: periodic snapshot(s)
 +
            of previous N intervals from transactions;
 +
%let interval = month;
 +
%let Begin    = %sysfunc(intnx(&interval,&today,-1,begin));
 +
%let End      = %sysfunc(intnx(&interval,&today,-1,end  ));
 +
%dateloop(start      = &begin
 +
        ,stop      = &end
 +
        ,interval  = week
 +
        ,MacroName  = daterpt0
 +
        ,MacroParms = %nrstr(data=library.testdata,var=date)
 +
        );
 +
**********/
 +
%Macro dateloop
 +
        (start      =0 /* integer of date */
 +
        ,stop      =2 /* integer of date */
 +
        ,interval  =day /* simple in (week month quarter year)
 +
/* intnx(interval: complex: week2==14 days week.2==Monday */
 +
        ,MacroName  =put note:
 +
        ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
 +
        ,semicolon  =0
 +
        ,MacroText  =.
 +
        ,testing    =0
 +
        )/des = 'site: calls reporting macro with dates'
 +
        /* ** store /* ** source /* */;
 +
%local D_Begin D_End Format MacroCall;
 +
%let Format = weekdate29.;
 +
%if "&MacroText" ne "."        %then %let MacroName = ;
 +
%if %scan(&MacroName,1) eq put %then %let Semicolon = 1;
 +
%let Testing = %eval(  &Testing      or &Semicolon
 +
                    or    %sysfunc(getoption(mprint )) eq MPRINT
 +
                        and %sysfunc(getoption(source2)) eq SOURCE2);
 +
%put &SysMacroName start: %sysfunc(putn(&start,&format));
 +
%put &SysMacroName  stop: %sysfunc(putn(&stop ,&format));
 +
%let D_Begin = %sysfunc(intnx(&Interval,&start,0,begin));
 +
 
 +
%do %until(&D_Begin gt &Stop);
 +
    %let D_End = %sysfunc(intnx(&Interval,&D_Begin,0,end  ));
 +
    %if "&MacroText" ne "." %then %do;
 +
        %local d dd ddd day day3;
 +
        %let d    = %sysfunc(weekday(&D_begin));
 +
        %let dd  = %sysfunc(putn(%sysfunc(day(&D_begin)),z2));
 +
        %let ddd  = %substr(%sysfunc(juldate7(&D_begin)),5,3);
 +
        %let day  = %sysfunc(putn(&D_begin,downame));
 +
        %let day3 = %substr(&Day,1,3);
 +
        %local mm mon month;
 +
        %let mm    = %sysfunc(putn(%sysfunc(month(&D_begin)),z2));
 +
        %let mon  = %sysfunc(putn(&D_begin,monname3));
 +
        %let month = %sysfunc(putn(&D_begin,monname));
 +
        %local ccyy qtr year yy;
 +
        %let qtr  = %sysfunc(qtr (&D_begin));
 +
        %let ccyy  = %sysfunc(year(&D_begin));
 +
        %let year  = &ccyy;
 +
        %let yy    = %substr(&ccyy,3,2);
 +
        %local week weekv weekw;
 +
        %let week  = %sysfunc(week(&D_begin,u));
 +
        %let weekv = %sysfunc(week(&D_begin,v));
 +
        %let weekw = %sysfunc(week(&D_begin,w));
 +
        %unquote(&MacroText)
 +
        %end;
 +
    %else %do;
 +
        %let MacroCall = &MacroName(;
 +
        %if %length(&MacroParms) %then
 +
            %let MacroCall = &MacroCall.%unquote(&MacroParms,);
 +
        %let MacroCall=&MacroCall.begin=&D_Begin,end=&D_End);
 +
        %&MacroCall
 +
        %end;
 +
    %if &Testing %then %do;
 +
        %put &SysMacroName: begin %sysfunc(putn(&D_Begin,&format));
 +
        %put &SysMacroName:  end %sysfunc(putn(&D_End  ,&format));
 +
        %end;
 +
    %if &Semicolon %then %do;
 +
        ;
 +
        %end;
 +
    %let D_Begin = %eval(&D_End +1);
 +
    %end;
 +
%mend dateloop;
 +
</source>
 +
 
 +
see also: [[Do_which_loop_until_or_while]]
 +
 
 +
==== Program DateLoop tests ====
 +
 
 +
<source lang="sas">
 +
*name: dateloop-testing.sas;
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
 
 +
%dateloop(start    = %sysfunc(intnx(quarter,&today,-1,sameday))
 +
        ,stop    = %sysfunc(intnx(month  ,&today, 0,begin  ))
 +
        ,interval = month
 +
        );
 +
%dateloop(start    = %sysfunc(intnx(month,&today,-1,sameday))
 +
        ,stop    = %sysfunc(intnx(week  ,&today, 0,begin  ))
 +
        ,interval = week
 +
        );
 +
%dateloop(start    = %sysfunc(intnx(week,&today,-1,sameday))
 +
        ,stop    = %sysfunc(intnx(day ,&today, 0,begin  ))
 +
        ,interval = day
 +
        );
 
</source>
 
</source>
  
 
log:
 
log:
 
<pre>
 
<pre>
14        %put sysdate9: &Sysdate9.;
+
DATELOOP start:   Wednesday, November 7, 2012
sysdate9: 15JAN2013
+
DATELOOP  stop:     Friday, February 1, 2013
15       
+
18        %let today = %sysevalf('21Dec2012'd);
+
  
23        %put today: &today %sysfunc(putn(&today,date9.));
+
DATELOOP:begin    Thursday, November 1, 2012
today: 19348 21DEC2012
+
DATELOOPend    Friday, November 30, 2012
24        %put today: &today %sysfunc(putn(&today,mmddyy10.));
+
 
today: 19348 12/21/2012
+
DATELOOP:begin    Saturday, December 1, 2012
25        %put today: &today %sysfunc(putn(&today,worddate18.));
+
DATELOOP: end    Monday, December 31, 2012
today: 19348 December 21, 2012
+
 
26        %put today: &today %sysfunc(putn(&today,weekdate29.));
+
DATELOOP:begin      Tuesday, January 1, 2013
today: 19348    Friday, December 21, 2012
+
DATELOOP: end    Thursday, January 31, 2013
27       
+
....
28        *parts:;
+
29        %put day/month: &today %sysfunc(day(&today));
+
day/month: 19348 21
+
30        %put month: &today %sysfunc(month(&today));
+
month: 19348 12
+
31         %put year: &today %sysfunc(year(&today));
+
year: 19348 2012
+
32       
+
33        %put week/year: &today %sysfunc(week(&today,u));
+
week/year: 19348 51
+
34        %put week/year: &today %sysfunc(week(&today,v));
+
week/year: 19348 51
+
35        %put week/year: &today %sysfunc(week(&today,w));
+
week/year: 19348 51
+
36       
+
37        %put day/year: &today %substr(%sysfunc(juldate7(&today)),5,3);
+
day/year: 19348 356
+
 
</pre>
 
</pre>
 +
 +
==== Program DateLoop tests integers ====
 +
 +
<source lang="sas">
 +
*name: dateloop-tests-integers.sas;
 +
%dateloop(start    = 0
 +
        ,stop    = 7
 +
        ,interval = day
 +
        );
 +
%dateloop(start    = 0
 +
        ,stop    = 92
 +
        ,interval = month
 +
        );
 +
%dateloop(start    = 0
 +
        ,stop    = 366
 +
        ,interval = quarter
 +
        );
 +
%dateloop(start    = 0
 +
        ,stop    = 366
 +
        ,interval = month
 +
        );
 +
</source>
 +
 +
log:
 +
<pre>
 +
1          *name: dateloop-tests-integers.sas;
 +
2          %dateloop(start    = 0
 +
3                  ,stop    = 7
 +
4                  ,interval = day
 +
5                  );
 +
DATELOOP start:      Friday, January 1, 1960
 +
DATELOOP  stop:      Friday, January 8, 1960
 +
 +
DATELOOP:begin      Friday, January 1, 1960
 +
DATELOOP:  end      Friday, January 1, 1960
 +
....
 +
DATELOOP:begin      Friday, January 8, 1960
 +
DATELOOP:  end      Friday, January 8, 1960
 +
 +
6          %dateloop(start  = 0
 +
7                  ,stop    = 92
 +
8                  ,interval = month
 +
9                  );
 +
DATELOOP start:      Friday, January 1, 1960
 +
DATELOOP  stop:      Saturday, April 2, 1960
 +
 +
DATELOOP:begin      Friday, January 1, 1960
 +
DATELOOP:  end      Sunday, January 31, 1960
 +
....
 +
DATELOOP:begin        Tuesday, March 1, 1960
 +
DATELOOP:  end      Thursday, March 31, 1960
 +
</pre>
 +
 +
==== Program DateLoop Previous 12 Months ====
 +
 +
<source lang="sas">
 +
/*    name: dateloop-demo-prev-12-months.sas;
 +
description: calling dateloop
 +
    purpose: template
 +
    /******/
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('2Oct2012'd);
 +
 +
*note: start is -1 year;
 +
%dateloop(start    = %sysfunc(intnx(year ,&today,-1,sameday))
 +
        ,stop    = %sysfunc(intnx(month,&today, 0,begin  ))
 +
        ,interval = month
 +
        );
 +
*note: start is -12 months;
 +
%dateloop(start    = %sysfunc(intnx(month,&today,-12,sameday))
 +
        ,stop    = %sysfunc(intnx(month,&today, 0 ,begin  ))
 +
        ,interval = month
 +
        );
 +
</source>
 +
 +
==== Program DateLoop Demo Previous Any ====
 +
 +
<source lang="sas">
 +
/*    name: dateloop-demo-prev-any.sas
 +
description: calling macro Date-Report-0
 +
    purpose: template
 +
to add for testing:
 +
        ,interval  = week
 +
        ,interval  = month
 +
        ,MacroName  = daterpt0
 +
        ,MacroParms = %nrstr(data=library.testdata,var=date)
 +
    /******/
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
 +
*choice: previous which?;
 +
%let interval = year;
 +
%let interval = quarter;
 +
%let interval = month;
 +
%let interval = week;
 +
 +
*report previous &interval by day;
 +
%dateloop(start    = %sysfunc(intnx(&interval.,&today,-1,begin))
 +
        ,stop    = %sysfunc(intnx(&interval.,&today,-1,end  ))
 +
        ,interval = day
 +
        );
 +
</source>
 +
 +
==== Program DateLoop Demo Previous Any Week Any Day ====
 +
 +
<source lang="sas">
 +
/*name    : dateloop-demo-prev-month-any-week-any-day.sas;
 +
description: macro dateloop caller for I-th day in J-th week
 +
            begin on day-of-week day in week-of-month week
 +
    purpose: template showing use of function nwkdom
 +
    /******/
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
 +
%let interval = month;*quarter year;
 +
 +
*parameters for function n-weekday-of-month;
 +
%let week_of_month = 2;*in 1:5;
 +
%let day_of_week  = 1;*in 1:7==Sunday--Saturday;
 +
%let day_of_week  = %sysfunc(weekday(&today));
 +
*let day_of_week  = 3;*Tuesday;
 +
 +
*calculate Previous day for function nwkdom usage;
 +
%let PrevDay  = %sysfunc(intnx(&interval,&today,-1,begin));
 +
 +
%dateloop(start    = %sysfunc(nwkdom(&week_of_month,&day_of_week
 +
                                        ,%sysfunc(month(&PrevDay))
 +
                                        ,%sysfunc(year (&PrevDay)) ))
 +
        ,stop    = %sysfunc(intnx(&interval,&today,-1,end))
 +
        ,interval = week.&day_of_week
 +
        );
 +
</source>
 +
 +
==== Program DateLoop Demo Previous Month By Week.shift-index ====
 +
 +
<source lang="sas">
 +
/*    name: dateloop-demo-prev-month-by-week.sas
 +
description: calling macro Date-Report-0
 +
    purpose: show usage of week.shift-index
 +
to add
 +
        ,MacroName  = daterpt0
 +
        ,MacroParms = %nrstr(data=library.testdata,var=date)
 +
    /******/
 +
options mprint;
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
 +
%let interval = month;
 +
%let date_start =%sysfunc(intnx(&interval.,&today,-1,begin));
 +
%let date_stop  =%sysfunc(intnx(&interval.,&today,-1,end  ));
 +
 +
*report first (short) week report includes first of month;
 +
%dateloop(start    = &date_start
 +
        ,stop    = &date_stop
 +
        ,interval = week
 +
        );
 +
*reports begin on Saturday==day=7 for Monday: week.2;
 +
%dateloop(start    = &date_start
 +
        ,stop    = &date_stop
 +
        ,interval = week.7
 +
        );
 +
*weekly reports begin on first: ThisDay in (1 8 15 22 29);
 +
%dateloop(start    = &date_start
 +
        ,stop    = &date_stop
 +
        ,interval = week.%sysfunc(weekday(&date_start))
 +
        );
 +
</source>
 +
log:
 +
<pre>
 +
17        *report first (short) week report includes first of month;
 +
18        %dateloop(start = &date_start
 +
19                  ,stop  = &date_stop
 +
21                  ,interval  = week
 +
22                  );
 +
DATELOOP start:      Tuesday, January 1, 2013
 +
DATELOOP  stop:    Thursday, January 31, 2013
 +
DATELOOP:begin    Sunday, December 30, 2012
 +
DATELOOP:  end    Saturday, January 5, 2013
 +
....
 +
23        *reports begin on Saturday==day=7 for Monday: week.2;
 +
24        %dateloop(start = &date_start
 +
25                  ,stop  = &date_stop
 +
27                  ,interval  = week.7
 +
28                  );
 +
DATELOOP start:      Tuesday, January 1, 2013
 +
DATELOOP  stop:    Thursday, January 31, 2013
 +
DATELOOP:begin  Saturday, December 29, 2012
 +
DATELOOP:  end      Friday, January 4, 2013
 +
....
 +
29        *weekly reports begin on first: ThisDay in (1 8 15 22 29);
 +
30        %dateloop(start = &date_start
 +
31                  ,stop  = &date_stop
 +
33                  ,interval  = week.%sysfunc(weekday(&date_start))
 +
34                  );
 +
DATELOOP start:      Tuesday, January 1, 2013
 +
DATELOOP  stop:    Thursday, January 31, 2013
 +
DATELOOP:begin      Tuesday, January 1, 2013
 +
DATELOOP:  end      Monday, January 7, 2013
 +
</pre>
 +
 +
=== Big Data Subset: Macro Date-Report-Zero ===
 +
 +
<source lang="sas">
 +
/*    name: DateRpt0.sas
 +
description: report with date-begin and -end
 +
    purpose: template for a periodic snapshot
 +
            an interval within a transaction table
 +
    /******/
 +
%MACRO daterpt0
 +
      (data    = sashelp.class
 +
      ,var    = age
 +
      ,begin  = 11
 +
      ,end    = 13
 +
      ,testing = 0
 +
      );
 +
ods _all_ close;
 +
ODS PDF
 +
    file = "demo-ods-%sysfunc(juldate7(&Date_Begin)).pdf"
 +
    ;
 +
PROC Print data = &Data.
 +
          (where = (&Date_Begin <= &Var <= &Date_End) );
 +
title3 "data = &Data. date.var: &Var in range "
 +
          "%sysfunc(putn(&Date_Begin,worddate18.)) "
 +
      "-- %sysfunc(putn(&Date_End  ,mmddyy10.  ))"
 +
      ;
 +
run;
 +
ods _all_ close;
 +
ods listing;
 +
%mend daterpt0;
 +
</source>
 +
 +
Notes:
 +
* juldate7 displays a date as ccYY-ddd == 2013-123
 +
* title and footnote can have multiple quoted strings;
 +
** remember to end each string with a space
 +
 +
==== Macro Date-Report-Zero Tests ====
 +
 +
<source lang="sas">
 +
/*    name: daterpt0-test.sas
 +
description: examples
 +
    purpose: unit test
 +
    /******/
 +
options mprint;
 +
%daterpt0();
 +
%daterpt0(data      =library.testdata
 +
        ,var        =date
 +
        ,date_begin =117
 +
        ,date_end  =139
 +
        );
 +
*previous week;
 +
%Let today = %sysfunc(today());
 +
%daterpt0(data      =library.testdata
 +
        ,var        =date
 +
        ,date_begin =%sysfunc(intnx(week,&today,-1,begin))
 +
        ,date_end  =%sysfunc(intnx(week,&today,-1,end  ))
 +
        );
 +
</source>
 +
 +
==== Macro Date-Report-Zero Demo Previous Any ====
 +
 +
<source lang="sas">
 +
/*    name: daterpt0-demo-prev-any.sas
 +
description: calling macro Date-Report-0
 +
    purpose: template
 +
    /******/
 +
options mprint;
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('2Oct2012'd);
 +
 +
*choice: previous which?;
 +
%let interval = year;
 +
%let interval = quarter;
 +
%let interval = month;
 +
%let interval = week;
 +
 +
%daterpt0(data      =library.testdata
 +
        ,var        =date
 +
        ,date_begin =%sysfunc(intnx(&interval.,&today,-1,begin))
 +
        ,date_end  =%sysfunc(intnx(&interval.,&today,-1,end  ))
 +
        );
 +
</source>
 +
 +
=== Macro Ordinal ===
 +
 +
This macro is Research-and-Development.
 +
 +
<source lang="sas">
 +
/*    name: ...\SAS-site\macros\ordinal.sas
 +
description: return an integer with suffix=ordinal in (0th,1st,2nd,3rd)
 +
    purpose: bells and whistles
 +
%*put _local_;
 +
    *******/
 +
%macro ordinal(n);
 +
%local tens trimmed suffix units;
 +
%let suffix = th;
 +
%*remove leading zeroes;
 +
%let trimmed = %eval(&N+0);
 +
%*what is the units digit?;
 +
%let units  = %sysfunc(mod(&N,10));
 +
%*what is the tens digit?;
 +
%let tens  = %eval(%sysfunc(mod(&N,100))/10);
 +
%if &tens ne 1 %then %do;
 +
  %if      &units eq 1 %then %let suffix = st;
 +
  %else %if &units eq 2 %then %let suffix = nd;
 +
  %else %if &units eq 3 %then %let suffix = rd;
 +
  %end;
 +
&trimmed.&suffix%mend ordinal;
 +
</source>
 +
 +
Testing
 +
 +
<source lang="sas">
 +
/* name: ordinal-test
 +
***/
 +
%put ordinal of 1:%ordinal(1)
 +
    ordinal of 2:%ordinal(2)
 +
    ordinal of 3:%ordinal(3)
 +
    ordinal of 4:%ordinal(4)
 +
    ;
 +
%Macro doloop(start=0, stop=14, step=1);
 +
%do N = &start %to &stop %by &step.;
 +
    %put n=&N: %ordinal(&n);
 +
    %end;
 +
    %mend;
 +
%doloop()
 +
%doloop(start=1,stop=31,step=10)
 +
%doloop(start=2,stop=22,step=10)
 +
%doloop(start=3,stop=33,step=10)
 +
%doloop(start=4,stop=34,step=10)
 +
%doloop(start=04,stop=44,step=10)
 +
%doloop(start=05,stop=55,step=20)
 +
%doloop(start=001,stop=101,step=20)
 +
</source>
 +
 +
=== Macro DateText ===
 +
 +
This macro is Research-and-Development.
 +
 +
<source lang="sas">
 +
/*    name: ...\SAS-site\macros\datetext.sas
 +
      note: same code in dateloop
 +
description: function to return text associated with a date
 +
purpose    : emphasize that dates as integers are an associative array
 +
usage notes: macro variables to be used in parameter text=%nrstr(&mvar)
 +
d    day of week 1:7
 +
dd    day of month 01:31
 +
ddd  day of year 001:366
 +
day  Sunday:Saturday
 +
day3  Sun:Sat
 +
mm    month of year 01:12
 +
mon  Jan:Dec
 +
month January:December
 +
q    of year 1:4
 +
qtr  of year 1:4
 +
ccyy  4-digit year
 +
year  4-digit year, same as ccyy
 +
yy    2-digit year, without century
 +
week  week.u
 +
weekv week.v
 +
weekw week.w
 +
 +
usage:
 +
%put
 +
%datetext(date =%sysfunc(today())
 +
        ,text =%nrstr(work.&year-&mm-&dd is &d/week &ddd/year)
 +
        );
 +
*******/
 +
%macro datetext
 +
        (date  =.
 +
        ,begin =.
 +
        ,end  =. /*not used*/
 +
        ,text  = /*%nrstr(work.&year-&mm-&d-&dd-&ddd)*/
 +
        )/des  = 'site: return text from date';
 +
%if &begin eq . %then %let begin=&date;
 +
%local d dd ddd day day3  mm mon month
 +
      ccyy q qtr year yy  week weekv weekw;
 +
%let d    = %sysfunc(weekday(&begin));
 +
%let dd    = %sysfunc(putn(%sysfunc(day(&begin)),z2));
 +
%let ddd  = %substr(%sysfunc(juldate7(&begin)),5,3);
 +
%let day  = %sysfunc(putn(&begin,downame));
 +
%let day3  = %substr(&day,1,3);
 +
%let mm    = %sysfunc(putn(%sysfunc(month(&begin)),z2));
 +
%let month = %sysfunc(putn(&begin,monname));
 +
%let mon  = %substr(&month,1,3);
 +
%let q    = %sysfunc(qtr (&begin));
 +
%let qtr  = &q;
 +
%let ccyy  = %sysfunc(year(&begin));
 +
%let year  = &ccyy;
 +
%let yy    = %substr(&ccyy,3,2);
 +
%let week  = %sysfunc(week(&begin,u));
 +
%let weekv = %sysfunc(week(&begin,v));
 +
%let weekw = %sysfunc(week(&begin,w));
 +
%unquote(&text)
 +
%mend datetext;
 +
</source>
 +
 +
Testing
 +
 +
<source lang="sas">
 +
/*    name: datetext-test.sas
 +
    *******/
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
 +
%put
 +
%datetext(date = 0
 +
          text = %nrstr(year:&year month:&Month day:&day)
 +
          );
 +
%put
 +
%datetext(date    = %sysfunc(intnx(week,&today,-1,begin))
 +
        ,text=
 +
  %nrstr(work.&year-&mm-&dd is %ordinal(&d).of.week &ddd..of.year)
 +
        );
 +
</source>
 +
 +
=== Small Data: Macro Year-Month ===
 +
 +
This macro is in the paper and is deprecated.
 +
Use the new-and-improved DateLoop with MacroText parameter.
 +
 +
<source lang="sas">
 +
%Macro ccYY_mm(date_begin=
 +
              ,date_end  =
 +
              ,library  =work);
 +
%local ccyy mm;
 +
%let ccyy = %sysfunc(year(&date_begin));
 +
%let mm  = %sysfunc(putn(%sysfunc(month(&date_begin)),z2));
 +
&Library..date&ccYY._&MM
 +
%mend;
 +
</source>
 +
 +
=== Adding Small Data: a list of dates  ===
 +
 +
<source lang="sas">
 +
/*    name: dateloop-demo-prev-12-months.sas;
 +
description: demo of calling dateloop
 +
    purpose: template
 +
    /******/
 +
*options mprint;
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
 +
%let library = work;
 +
 +
%let ccyy = %sysfunc(year(&today));
 +
%let prev_month  =%sysfunc(intnx(month,&today,-1 ,begin ));
 +
%let mm  = %sysfunc(putn(%sysfunc(month(&prev_month)),z2));
 +
 +
*note: start is -12 months;
 +
DATA %dateloop
 +
      (start    = %sysfunc(intnx(month,&today,-12,sameday))
 +
      ,stop      = %sysfunc(intnx(month,&today,-1 ,end  ))
 +
      ,interval  = month
 +
      ,MacroText = %nrstr(&Library..date&ccYY._&MM)
 +
      );
 +
do until(EndoFile);
 +
  set sashelp.class end = EndoFile;
 +
  output work.date&ccYY._&MM;
 +
  end;
 +
stop;
 +
 +
DATA Previous_12;
 +
do until(EndoFile);
 +
  set %dateloop
 +
      (start    = %sysfunc(intnx(month,&today,-12,sameday))
 +
      ,stop      = %sysfunc(intnx(month,&today,-1 ,end  ))
 +
      ,interval  = month
 +
      ,MacroText = %nrstr(&Library..date&ccYY._&MM)
 +
      ) end = EndoFile;
 +
  output;
 +
  end;
 +
stop;
 +
run;
 +
</source>
  
 
== References ==
 
== References ==
  
* Function nwkdom: N weekday of month :: find date of WDth weekday in Wth week of month
+
* The value Zero as a date in Excel(TM)
** [[Generating_Holiday_Lists]]
+
** http://www.cpearson.com/excel/datetime.htm
* Handling Dates in the Macro Facility
+
* Erik W. Tilanus
** Toby Dunn
+
** Dating SAS® and MS Excel
** http://analytics.ncsu.edu/sesug/2006/SC11_06.PDF
+
** http://www2.sas.com/proceedings/sugi29/068-29.pdf
** http://www.nesug.org/proceedings/nesug06/cc/cc21.pdf
+
* Toby Dunn
** http://analytics.ncsu.edu/sesug/2009/FF004.Dunn.pdf
+
** Handling Dates in the Macro Facility
* It’s Five O’Clock Somewhere!!! Handling Dates And Times In SAS®
+
*** http://analytics.ncsu.edu/sesug/2006/SC11_06.PDF
** Toby Dunn and Sarah Woodruff
+
*** http://www.nesug.org/proceedings/nesug06/cc/cc21.pdf
 +
*** http://analytics.ncsu.edu/sesug/2009/FF004.Dunn.pdf
 +
* Toby Dunn and Sarah Woodruff
 +
** It’s Five O’Clock Somewhere!!! Handling Dates And Times In SAS®
 
** http://analytics.ncsu.edu/sesug/2010/FF10.Dunn.pdf
 
** http://analytics.ncsu.edu/sesug/2010/FF10.Dunn.pdf
 
* R. J. Fehd
 
* R. J. Fehd
 
** [[Date_datetime_time_stamp]]
 
** [[Date_datetime_time_stamp]]
** [[Macro_Clock]]
+
** [[Do_which_loop_until_or_while]]
** [[Macro_Used_Real_Time]]
+
** [[Macro_CallMacr]] is the predecessor of Macro DateLoop
** [[Using_Sysfunc_to_date-stamp_data_set_names]]
+
** [[Macro_CallText]]
* Pretty Dates All in a Row
+
** [[Macro_Do-Loop]]
** Dianne Louise Rhodes
+
** [[Macro_Do_Loop_Continue_or_Leave]]
 +
** [[Macro_TextLine]]
 +
** [[Option_Dtreset]] for updating datetime-stamp in title1
 +
* Wayne Finley
 +
** A Beginners Guide to SAS® Date and Time Handling
 +
*** http://www2.sas.com/proceedings/sugi25/25/btu/25p058.pdf
 +
** What Fiscal Year is this and when does it start and end?
 +
*** http://www2.sas.com/proceedings/sugi25/25/cc/25p084.pdf
 +
* Andrew Karp
 +
** Working With SAS® Date And Time Functions
 +
** www2.sas.com/proceedings/sugi24/Begtutor/p58-24.pdf
 +
* Dianne Louise Rhodes
 +
** Pretty Dates All in a Row
 
** http://www2.sas.com/proceedings/sugi31/015-31.pdf
 
** http://www2.sas.com/proceedings/sugi31/015-31.pdf
 +
* Howard Schreier
 +
** [[Generating_Holiday_Lists]]
 +
** nwkdom: Nth weekday of month :: find date of Nth weekday in Wth week of month
 +
* Sarah Woodruff and Toby Dunn
 +
** Understanding and Controlling Your Do-Loops
 +
** http://analytics.ncsu.edu/sesug/2010/FF01.Woodruff.pdf
 +
 +
=== References for working with Time ===
 +
 +
* [[Date_datetime_time_stamp]]
 +
* [[Macro_Clock]]
 +
* [[Macro_Used_Real_Time]]
 +
* [[Passing_Real_Numbers_as_Macro_Variables]]
 +
* [[Using_Sysfunc_to_date-stamp_data_set_names]]
  
 
--[[User:Ron.Fehd.macro.maven|Ronald_J._Fehd macro.maven == the radical programmer]] 18:43, 15 January 2013 (EST)
 
--[[User:Ron.Fehd.macro.maven|Ronald_J._Fehd macro.maven == the radical programmer]] 18:43, 15 January 2013 (EST)
  
 +
[[Category:Design_Elements_for_Macros]]
 +
[[Category:Functions_and_CALL_Routines]]
 +
[[Category:INTNX_Function]]
 
[[Category:Macros_by_Ron_Fehd]]
 
[[Category:Macros_by_Ron_Fehd]]
 +
[[Category:MONTH_Function]]
 +
[[Category:NWKDOM_Function]]
 +
[[Category:Papers_by_Ron_Fehd]]
 +
[[Category:PUTN_Function]]
 +
[[Category:%25SYSEVALF_Function]]
 +
[[Category:%25SYSFUNC_Function]]
 +
[[Category:WEEKDAY_Function]]
 +
[[Category:YEAR_Function]]

Latest revision as of 09:39, 27 October 2014

Writing Macro Loops with Dates from Then to Now

Author/editor: Ronald_J._Fehd macro maven

This paper has been published in SGF2013.QT: Quick Tips, formerly known as Coders Corner.

http://support.sas.com/resources/papers/proceedings13/343-2013.pdf

Audience: macro programmers

Overview

This page contains the programs, macros, tests, and calling programs in the paper

Theory

Overview

  • Theory
    • macro language produces text
    • macro loops require numbers
    • a date is a reference to a number, which is an index to an associative array
    • given a number, a macro returns text from the date associative array
  • Practice: macro as Black Box
    • macro DateLoop need numbers for its loop: start, stop and step
    • it calculates Begin and End of a date interval
    • and calls a macro subroutine
      • -- either Date-Report-Zero or Year-Month --
      • with parameters of Begin and End
  • macro subroutine can do either of
    • Small Data: Year-Month to return list of smaller-intervaled data sets
    • Big Data: Date-Report-Zero which is a Periodic Snapshot, a subset of Big Data with dates between beginning and end of a time interval

Programs showing Bytes of Theory

Program fips-codes

This program shows that the associative array of two-letter state codes has an index: the (U.S.) Federal Information Processing Standard (FIPS) number.

 /*    name: fips-codes
description: demo of associative array
purpose    : for slide
*******/
*system functions;
data work.State_Info;
attrib fips  length = 4
       St    length = $2
       State length = $22
       ;
do fips  = 1 to 79;
   St    = fipstate(fips);
   State = fipnamel(fips);
   if St ne '--' then output;
   end;
stop;
 
PROC Print data = &Syslast;

A Date is a Reference to an Integer

Use any of these macro assignment statements to get an integer of a date for use with macro loops.

*integers;
%let today = 0;
*let today = 366;
*functions;
*let today = %sysfunc(today());
%let today = %sysfunc(mdy(04,1,2013));
*date literal;
*let today = %sysevalf('30Apr2013'd);
*let today = %sysevalf('14Mar2013'd);
*let today = %sysevalf('01May2013'd);
 
*display: note other formats;
%put today: &today %sysfunc(putn(&today,weekdate29.));

Program Info-in-Dates

This program shows over a dozen items of information available from the date associative array.

 /*    name: info-in-dates.sas
description: provide associative array of values from dates
purpose    : show how to convert dates to N and name
             for ODS date-stamped filenames
sas.help: About SAS Date, Time, and Datetime Values
          Dictionary of Functions and CALL Routines
          Working with Dates in the SAS System:
             Comparing Durations and SAS Date Values
             Understanding How SAS Handles Dates
reference:
  http://www.sascommunity.org/wiki/Date_datetime_time_stamp
*******/
%let today = 0;
%let today = 366;
*let today = %sysfunc(today());
*let today = %sysevalf('30Apr2013'd);
%let today = %sysfunc(mdy(04,01,2013));
options nosource;
%put today: &today %sysfunc(putn(&today,weekdate29.));
 
%put day/month.N  :%sysfunc(day(&today));
%put day/month.z2 :%sysfunc(putn(%sysfunc(day(&today)),z2));
%put day/week.N   :%sysfunc(weekday(&today));
%put day/week.name:%sysfunc(putn(&today,downame));
%put day/year     :%substr(%sysfunc(juldate7(&today)),5,3);
 
%put month.N    :%sysfunc(month(&today));
%put month.z2   :%sysfunc(putn(%sysfunc(month(&today)),z2));
%put month.name :%sysfunc(putn(&today,monname));
%put month.name3:%sysfunc(putn(&today,monname3));
%put year       :%sysfunc(year(&today));
 
%put week/year.u:%sysfunc(week(&today,u));
%put week/year.v:%sysfunc(week(&today,v));
%put week/year.w:%sysfunc(week(&today,w));

log:

17         options nosource;
today: 366       Sunday, January 1, 1961
day/month.N  : 1
day/month.z2 : 01
day/week.N   : 1
day/week.name:    Sunday
day/year     : 001
month.N    : 1
month.z2   : 01
month.name :   January
month.name3: Jan
year       : 1961
week/year.u: 1
week/year.v: 52
week/year.w: 0

see also:

Note: these functions can be use in ODS filenames.

Syntax of Interval-Indexing function intnx

The Interval-Indexing function intnx saves the headaches of off-by-one errors when calculating previous begin- and end-dates of intervals.

Notes:

  • interval
    • a simple interval is week == 7 days
    • a complex interval is week2 == week*2 = 14 days
    • for fiscal year use qtr.N
  • date must be a number, not a reference like a date literal
  • increment:
    • negative means previous interval
    • zero means this time-interval, e.g.: week
    • positive means future
  • alignment
    • mid-month == month.15
    • sameday: e.g.: day.2 of last week or last.month.21st
intnx(interval  = (simple : day, week, month, ...)
                   complex: week2, week.2 qtr.2)
     ,start-from= date as integer
     ,increment = (-12, -1, 0)
     ,alignment = (begin, middle, end, same)
*name: demo-mvar-basics.sas;
 
* assignment;
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
%let interval = week;
%let format = weekdate29.;
 
* calculations;
%let D_Begin  =%sysfunc(intnx(&Interval,&today,0,begin));
%let D_Middle =%sysfunc(intnx(&Interval,&today,0,middle));
%let D_End    =%sysfunc(intnx(&Interval,&today,0,end  ));
 
* display;
%put today: &today %sysfunc(putn(&today,worddate18.));
%put today: &today %sysfunc(putn(&today,&format));
%put D_Begin : &D_Begin %sysfunc(putn(&D_Begin ,&format));
%put D_Middle: &D_Begin %sysfunc(putn(&D_Middle,&format));
%put D_End   : &D_End   %sysfunc(putn(&D_End   ,&format));


Programs

Program Test-Data

 /*    name: test-data
description: provide data with dates
purpose    : for use by other demo programs
usage:
%daterpt0(data = Library.TestData
         ,var  = date
         ,...);
         /*******/
DATA Library.TestData;
     attrib EntityId length = 4
            Date     length = 8 format = weekdate17.
            Fact     length = 8;
do Date = 0 to '01May2013'd;
   do EntityId = int(Date/17);
      do Value = int(Date**3/19);
         output;
         end;
      end;
   end;
stop;
run;

Macros

  1. Save these two macros in a folder 'C:\SAS-site\macros'
  2. add this code to your autoexec:
Filename Project  '.';
Libname  Library  '..\sas7b';
* macros: autocall: *.sas;
options  MautoSource
         SASautos = (Project 'C:\SAS-site\macros' SASautos);

The above code is from: Setting_Up_Project_Config_and_AutoExec

Macro DateLoop

Macro DateLoop

note: This is New and Improved, different from the macro in the paper.

 /*    name: ...\SAS-site\macros\dateloop.sas
     author: Ronald J. Fehd  2013-March-28
description: macro function do loop from loop-start to loop-stop
purpose: standardize calling of report macros for many intervals
NOTES: called macro must have parameters Begin and End
sas.help: Incrementing Dates and Times
             by Using Multipliers and by Shifting Intervals
sas.wiki: http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
    http://www.sascommunity.org/wiki/Do_which_until_or_while
predecessor: http://www.sascommunity.org/wiki/Macro_CallMacr
usage: demo
%let today    = %sysfunc(today());
%let interval = week;
%let Begin    = %sysfunc(intnx(&interval,&today,-12,begin));
%let End      = %sysfunc(intnx(&interval,&today,- 1,end  ));
* testing;
%dateloop(start    = &begin
         ,stop     = &end
         ,interval = day
         )
* small data: concatenate monthly data sets for previous-12 report;
%put
%dateloop(start    = &begin
         ,stop     = &end
         ,interval = month
         ,MacroText= %nrstr(work.Year_Month_&year._&mm._&month)
         );
* big data: periodic snapshot(s)
            of previous N intervals from transactions;
%let interval = month;
%let Begin    = %sysfunc(intnx(&interval,&today,-1,begin));
%let End      = %sysfunc(intnx(&interval,&today,-1,end  ));
%dateloop(start      = &begin
         ,stop       = &end
         ,interval   = week
         ,MacroName  = daterpt0
         ,MacroParms = %nrstr(data=library.testdata,var=date)
         );
**********/
%Macro dateloop
         (start      =0 /* integer of date */
         ,stop       =2 /* integer of date */
         ,interval   =day /* simple in (week month quarter year)
 /* intnx(interval: complex: week2==14 days week.2==Monday */
         ,MacroName  =put note:
         ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
         ,semicolon  =0
         ,MacroText  =.
         ,testing    =0
         )/des = 'site: calls reporting macro with dates'
         /* ** store /* ** source /* */;
%local D_Begin D_End Format MacroCall;
%let Format = weekdate29.;
%if "&MacroText" ne "."        %then %let MacroName = ;
%if %scan(&MacroName,1) eq put %then %let Semicolon = 1;
%let Testing = %eval(   &Testing      or &Semicolon
                     or     %sysfunc(getoption(mprint )) eq MPRINT
                        and %sysfunc(getoption(source2)) eq SOURCE2);
%put &SysMacroName start: %sysfunc(putn(&start,&format));
%put &SysMacroName  stop: %sysfunc(putn(&stop ,&format));
%let D_Begin = %sysfunc(intnx(&Interval,&start,0,begin));
 
%do %until(&D_Begin gt &Stop);
    %let D_End = %sysfunc(intnx(&Interval,&D_Begin,0,end  ));
    %if "&MacroText" ne "." %then %do;
        %local d dd ddd day day3;
        %let d    = %sysfunc(weekday(&D_begin));
        %let dd   = %sysfunc(putn(%sysfunc(day(&D_begin)),z2));
        %let ddd  = %substr(%sysfunc(juldate7(&D_begin)),5,3);
        %let day  = %sysfunc(putn(&D_begin,downame));
        %let day3 = %substr(&Day,1,3);
        %local mm mon month;
        %let mm    = %sysfunc(putn(%sysfunc(month(&D_begin)),z2));
        %let mon   = %sysfunc(putn(&D_begin,monname3));
        %let month = %sysfunc(putn(&D_begin,monname));
        %local ccyy qtr year yy;
        %let qtr   = %sysfunc(qtr (&D_begin));
        %let ccyy  = %sysfunc(year(&D_begin));
        %let year  = &ccyy;
        %let yy    = %substr(&ccyy,3,2);
        %local week weekv weekw;
        %let week  = %sysfunc(week(&D_begin,u));
        %let weekv = %sysfunc(week(&D_begin,v));
        %let weekw = %sysfunc(week(&D_begin,w));
        %unquote(&MacroText)
        %end;
    %else %do;
        %let MacroCall = &MacroName(;
        %if %length(&MacroParms) %then
            %let MacroCall = &MacroCall.%unquote(&MacroParms,);
        %let MacroCall=&MacroCall.begin=&D_Begin,end=&D_End);
        %&MacroCall
        %end;
    %if &Testing %then %do;
        %put &SysMacroName: begin %sysfunc(putn(&D_Begin,&format));
        %put &SysMacroName:   end %sysfunc(putn(&D_End  ,&format));
        %end;
    %if &Semicolon %then %do;
        ;
        %end;
    %let D_Begin = %eval(&D_End +1);
    %end;
%mend dateloop;

see also: Do_which_loop_until_or_while

Program DateLoop tests

*name: dateloop-testing.sas;
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
 
%dateloop(start    = %sysfunc(intnx(quarter,&today,-1,sameday))
         ,stop     = %sysfunc(intnx(month  ,&today, 0,begin  ))
         ,interval = month
         );
%dateloop(start    = %sysfunc(intnx(month,&today,-1,sameday))
         ,stop     = %sysfunc(intnx(week  ,&today, 0,begin  ))
         ,interval = week
         );
%dateloop(start    = %sysfunc(intnx(week,&today,-1,sameday))
         ,stop     = %sysfunc(intnx(day ,&today, 0,begin  ))
         ,interval = day
         );

log:

DATELOOP start:   Wednesday, November 7, 2012
DATELOOP  stop:      Friday, February 1, 2013

DATELOOP:begin    Thursday, November 1, 2012
DATELOOP:  end     Friday, November 30, 2012

DATELOOP:begin    Saturday, December 1, 2012
DATELOOP:  end     Monday, December 31, 2012

DATELOOP:begin      Tuesday, January 1, 2013
DATELOOP:  end    Thursday, January 31, 2013
....

Program DateLoop tests integers

*name: dateloop-tests-integers.sas;
%dateloop(start    = 0
         ,stop     = 7
         ,interval = day
         );
%dateloop(start    = 0
         ,stop     = 92
         ,interval = month
         );
%dateloop(start    = 0
         ,stop     = 366
         ,interval = quarter
         );
%dateloop(start    = 0
         ,stop     = 366
         ,interval = month
         );

log:

1          *name: dateloop-tests-integers.sas;
2          %dateloop(start    = 0
3                   ,stop     = 7
4                   ,interval = day
5                   );
DATELOOP start:       Friday, January 1, 1960
DATELOOP  stop:       Friday, January 8, 1960

DATELOOP:begin       Friday, January 1, 1960
DATELOOP:  end       Friday, January 1, 1960
....
DATELOOP:begin       Friday, January 8, 1960
DATELOOP:  end       Friday, January 8, 1960

6          %dateloop(start   = 0
7                   ,stop    = 92
8                  ,interval = month
9                  );
DATELOOP start:       Friday, January 1, 1960
DATELOOP  stop:       Saturday, April 2, 1960

DATELOOP:begin       Friday, January 1, 1960
DATELOOP:  end      Sunday, January 31, 1960
....
DATELOOP:begin        Tuesday, March 1, 1960
DATELOOP:  end      Thursday, March 31, 1960

Program DateLoop Previous 12 Months

 /*    name: dateloop-demo-prev-12-months.sas;
description: calling dateloop
    purpose: template
    /******/
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('2Oct2012'd);
 
*note: start is -1 year;
%dateloop(start    = %sysfunc(intnx(year ,&today,-1,sameday))
         ,stop     = %sysfunc(intnx(month,&today, 0,begin  ))
         ,interval = month
         );
*note: start is -12 months;
%dateloop(start    = %sysfunc(intnx(month,&today,-12,sameday))
         ,stop     = %sysfunc(intnx(month,&today, 0 ,begin  ))
         ,interval = month
         );

Program DateLoop Demo Previous Any

 /*    name: dateloop-demo-prev-any.sas
description: calling macro Date-Report-0
    purpose: template
to add for testing:
         ,interval   = week
         ,interval   = month
         ,MacroName  = daterpt0
         ,MacroParms = %nrstr(data=library.testdata,var=date)
    /******/
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
 
*choice: previous which?;
%let interval = year;
%let interval = quarter;
%let interval = month;
%let interval = week;
 
*report previous &interval by day;
%dateloop(start    = %sysfunc(intnx(&interval.,&today,-1,begin))
         ,stop     = %sysfunc(intnx(&interval.,&today,-1,end  ))
         ,interval = day
         );

Program DateLoop Demo Previous Any Week Any Day

 /*name    : dateloop-demo-prev-month-any-week-any-day.sas;
description: macro dateloop caller for I-th day in J-th week
             begin on day-of-week day in week-of-month week
    purpose: template showing use of function nwkdom
    /******/
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
 
%let interval = month;*quarter year;
 
*parameters for function n-weekday-of-month;
%let week_of_month = 2;*in 1:5;
%let day_of_week   = 1;*in 1:7==Sunday--Saturday;
%let day_of_week   = %sysfunc(weekday(&today));
*let day_of_week   = 3;*Tuesday;
 
*calculate Previous day for function nwkdom usage;
%let PrevDay   = %sysfunc(intnx(&interval,&today,-1,begin));
 
%dateloop(start    = %sysfunc(nwkdom(&week_of_month,&day_of_week
                                         ,%sysfunc(month(&PrevDay))
                                         ,%sysfunc(year (&PrevDay)) ))
         ,stop     = %sysfunc(intnx(&interval,&today,-1,end))
         ,interval = week.&day_of_week
         );

Program DateLoop Demo Previous Month By Week.shift-index

 /*    name: dateloop-demo-prev-month-by-week.sas
description: calling macro Date-Report-0
    purpose: show usage of week.shift-index
to add
         ,MacroName  = daterpt0
         ,MacroParms = %nrstr(data=library.testdata,var=date)
    /******/
options mprint;
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
 
%let interval = month;
%let date_start =%sysfunc(intnx(&interval.,&today,-1,begin));
%let date_stop  =%sysfunc(intnx(&interval.,&today,-1,end  ));
 
*report first (short) week report includes first of month;
%dateloop(start    = &date_start
         ,stop     = &date_stop
         ,interval = week
         );
*reports begin on Saturday==day=7 for Monday: week.2;
%dateloop(start    = &date_start
         ,stop     = &date_stop
         ,interval = week.7
         );
*weekly reports begin on first: ThisDay in (1 8 15 22 29);
%dateloop(start    = &date_start
         ,stop     = &date_stop
         ,interval = week.%sysfunc(weekday(&date_start))
         );

log:

17         *report first (short) week report includes first of month;
18         %dateloop(start = &date_start
19                  ,stop  = &date_stop
21                  ,interval   = week
22                  );
DATELOOP start:      Tuesday, January 1, 2013
DATELOOP  stop:    Thursday, January 31, 2013
DATELOOP:begin     Sunday, December 30, 2012
DATELOOP:  end     Saturday, January 5, 2013
....
23         *reports begin on Saturday==day=7 for Monday: week.2;
24         %dateloop(start = &date_start
25                  ,stop  = &date_stop
27                  ,interval   = week.7
28                  );
DATELOOP start:      Tuesday, January 1, 2013
DATELOOP  stop:    Thursday, January 31, 2013
DATELOOP:begin   Saturday, December 29, 2012
DATELOOP:  end       Friday, January 4, 2013
....
29         *weekly reports begin on first: ThisDay in (1 8 15 22 29);
30         %dateloop(start = &date_start
31                  ,stop  = &date_stop
33                  ,interval   = week.%sysfunc(weekday(&date_start))
34                  );
DATELOOP start:      Tuesday, January 1, 2013
DATELOOP  stop:    Thursday, January 31, 2013
DATELOOP:begin      Tuesday, January 1, 2013
DATELOOP:  end       Monday, January 7, 2013

Big Data Subset: Macro Date-Report-Zero

 /*    name: DateRpt0.sas
description: report with date-begin and -end
    purpose: template for a periodic snapshot
             an interval within a transaction table
    /******/
%MACRO daterpt0
       (data    = sashelp.class
       ,var     = age
       ,begin   = 11
       ,end     = 13
       ,testing = 0
       );
ods _all_ close;
ODS PDF
    file = "demo-ods-%sysfunc(juldate7(&Date_Begin)).pdf"
    ;
PROC Print data = &Data.
          (where = (&Date_Begin <= &Var <= &Date_End) );
title3 "data = &Data. date.var: &Var in range "
          "%sysfunc(putn(&Date_Begin,worddate18.)) "
       "-- %sysfunc(putn(&Date_End  ,mmddyy10.  ))"
       ;
run;
ods _all_ close;
ods listing;
%mend daterpt0;

Notes:

  • juldate7 displays a date as ccYY-ddd == 2013-123
  • title and footnote can have multiple quoted strings;
    • remember to end each string with a space

Macro Date-Report-Zero Tests

 /*    name: daterpt0-test.sas
description: examples
    purpose: unit test
    /******/
options mprint;
%daterpt0();
%daterpt0(data       =library.testdata
         ,var        =date
         ,date_begin =117
         ,date_end   =139
         );
*previous week;
%Let today = %sysfunc(today());
%daterpt0(data       =library.testdata
         ,var        =date
         ,date_begin =%sysfunc(intnx(week,&today,-1,begin))
         ,date_end   =%sysfunc(intnx(week,&today,-1,end  ))
         );

Macro Date-Report-Zero Demo Previous Any

 /*    name: daterpt0-demo-prev-any.sas
description: calling macro Date-Report-0
    purpose: template
    /******/
options mprint;
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('2Oct2012'd);
 
*choice: previous which?;
%let interval = year;
%let interval = quarter;
%let interval = month;
%let interval = week;
 
%daterpt0(data       =library.testdata
         ,var        =date
         ,date_begin =%sysfunc(intnx(&interval.,&today,-1,begin))
         ,date_end   =%sysfunc(intnx(&interval.,&today,-1,end  ))
         );

Macro Ordinal

This macro is Research-and-Development.

 /*    name: ...\SAS-site\macros\ordinal.sas
description: return an integer with suffix=ordinal in (0th,1st,2nd,3rd)
    purpose: bells and whistles
%*put _local_;
    *******/
%macro ordinal(n);
%local tens trimmed suffix units;
%let suffix = th;
%*remove leading zeroes;
%let trimmed = %eval(&N+0);
%*what is the units digit?;
%let units   = %sysfunc(mod(&N,10));
%*what is the tens digit?;
%let tens   = %eval(%sysfunc(mod(&N,100))/10);
%if &tens ne 1 %then %do;
   %if       &units eq 1 %then %let suffix = st;
   %else %if &units eq 2 %then %let suffix = nd;
   %else %if &units eq 3 %then %let suffix = rd;
   %end;
&trimmed.&suffix%mend ordinal;

Testing

 /* name: ordinal-test
***/
%put ordinal of 1:%ordinal(1)
     ordinal of 2:%ordinal(2)
     ordinal of 3:%ordinal(3)
     ordinal of 4:%ordinal(4)
     ;
%Macro doloop(start=0, stop=14, step=1);
%do N = &start %to &stop %by &step.;
    %put n=&N: %ordinal(&n);
    %end;
    %mend;
%doloop()
%doloop(start=1,stop=31,step=10)
%doloop(start=2,stop=22,step=10)
%doloop(start=3,stop=33,step=10)
%doloop(start=4,stop=34,step=10)
%doloop(start=04,stop=44,step=10)
%doloop(start=05,stop=55,step=20)
%doloop(start=001,stop=101,step=20)

Macro DateText

This macro is Research-and-Development.

/*     name: ...\SAS-site\macros\datetext.sas
       note: same code in dateloop
description: function to return text associated with a date
purpose    : emphasize that dates as integers are an associative array
usage notes: macro variables to be used in parameter text=%nrstr(&mvar)
d     day of week 1:7
dd    day of month 01:31
ddd   day of year 001:366
day   Sunday:Saturday
day3  Sun:Sat
mm    month of year 01:12
mon   Jan:Dec
month January:December
q     of year 1:4
qtr   of year 1:4
ccyy  4-digit year
year  4-digit year, same as ccyy
yy    2-digit year, without century
week  week.u
weekv week.v
weekw week.w
 
usage:
%put
%datetext(date =%sysfunc(today())
         ,text =%nrstr(work.&year-&mm-&dd is &d/week &ddd/year)
         );
*******/
%macro datetext
         (date  =.
         ,begin =.
         ,end   =. /*not used*/
         ,text  = /*%nrstr(work.&year-&mm-&d-&dd-&ddd)*/
         )/des  = 'site: return text from date';
%if &begin eq . %then %let begin=&date;
%local d dd ddd day day3   mm mon month
       ccyy q qtr year yy  week weekv weekw;
%let d     = %sysfunc(weekday(&begin));
%let dd    = %sysfunc(putn(%sysfunc(day(&begin)),z2));
%let ddd   = %substr(%sysfunc(juldate7(&begin)),5,3);
%let day   = %sysfunc(putn(&begin,downame));
%let day3  = %substr(&day,1,3);
%let mm    = %sysfunc(putn(%sysfunc(month(&begin)),z2));
%let month = %sysfunc(putn(&begin,monname));
%let mon   = %substr(&month,1,3);
%let q     = %sysfunc(qtr (&begin));
%let qtr   = &q;
%let ccyy  = %sysfunc(year(&begin));
%let year  = &ccyy;
%let yy    = %substr(&ccyy,3,2);
%let week  = %sysfunc(week(&begin,u));
%let weekv = %sysfunc(week(&begin,v));
%let weekw = %sysfunc(week(&begin,w));
%unquote(&text)
%mend datetext;

Testing

 /*    name: datetext-test.sas
    *******/
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
 
%put
%datetext(date = 0
          text = %nrstr(year:&year month:&Month day:&day)
          );
%put
%datetext(date    = %sysfunc(intnx(week,&today,-1,begin))
         ,text=
   %nrstr(work.&year-&mm-&dd is %ordinal(&d).of.week &ddd..of.year)
         );

Small Data: Macro Year-Month

This macro is in the paper and is deprecated. Use the new-and-improved DateLoop with MacroText parameter.

%Macro ccYY_mm(date_begin=
              ,date_end  =
              ,library   =work);
%local ccyy mm;
%let ccyy = %sysfunc(year(&date_begin));
%let mm   = %sysfunc(putn(%sysfunc(month(&date_begin)),z2));
&Library..date&ccYY._&MM
%mend;

Adding Small Data: a list of dates

 /*    name: dateloop-demo-prev-12-months.sas;
description: demo of calling dateloop
    purpose: template
    /******/
*options mprint;
%let today = %sysfunc(today());
*let today = %sysfunc(mdy(5,1,2013));
*let today = %sysevalf('01May2013'd);
 
%let library = work;
 
%let ccyy = %sysfunc(year(&today));
%let prev_month  =%sysfunc(intnx(month,&today,-1 ,begin ));
%let mm   = %sysfunc(putn(%sysfunc(month(&prev_month)),z2));
 
*note: start is -12 months;
DATA %dateloop
       (start     = %sysfunc(intnx(month,&today,-12,sameday))
       ,stop      = %sysfunc(intnx(month,&today,-1 ,end  ))
       ,interval  = month
       ,MacroText = %nrstr(&Library..date&ccYY._&MM)
       );
do until(EndoFile);
   set sashelp.class end = EndoFile;
   output work.date&ccYY._&MM;
   end;
stop;
 
DATA Previous_12;
do until(EndoFile);
   set %dateloop
       (start     = %sysfunc(intnx(month,&today,-12,sameday))
       ,stop      = %sysfunc(intnx(month,&today,-1 ,end  ))
       ,interval  = month
       ,MacroText = %nrstr(&Library..date&ccYY._&MM)
       ) end = EndoFile;
   output;
   end;
stop;
run;

References

References for working with Time

--Ronald_J._Fehd macro.maven == the radical programmer 18:43, 15 January 2013 (EST)