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 date value zero in SAS and Excel)
(References for working with Time)
 
(6 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: [[User:Ron.Fehd.macro.maven|Ronald_J._Fehd macro maven]]
+
Author/editor: [[User:Ron.Fehd.macro.maven|Ronald_J._Fehd macro maven]]
  
This paper is accepted for SGF2013.QT: Quick Tips, formerly known as Coders Corner.
+
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
 
Audience: macro programmers
Line 12: Line 14:
 
in the paper
 
in the paper
  
== Programs ==
+
== Theory ==
  
=== Program Test-Data ===
+
'''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: test-data
+
  /*    name: fips-codes
description: provide data with dates
+
description: demo of associative array
purpose    : for use by other demo programs
+
purpose    : for slide
usage:
+
*******/
%daterpt0(data = Library.TestData
+
*system functions;
        ,var  = date
+
data work.State_Info;
        ,...);
+
attrib fips  length = 4
        /*******/
+
      St    length = $2
DATA Library.TestData;
+
      State length = $22
    attrib EntityId length = 4
+
      ;
            Date    length = 8 format = weekdate17.
+
do fips  = 1 to 79;
            Fact    length = 8;
+
   St    = fipstate(fips);
do Date = 0 to 3*370;
+
  State = fipnamel(fips);
   do EntityId = int(Date/17);
+
  if St ne '--' then output;
      do Value = int(Date**3/19);
+
        output;
+
        end;
+
      end;
+
 
   end;
 
   end;
 
stop;
 
stop;
run;</source>
 
  
=== Program Info-in-Dates ===
+
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">
 
<source lang="sas">
Line 58: Line 105:
 
*let today = %sysfunc(today());
 
*let today = %sysfunc(today());
 
*let today = %sysevalf('30Apr2013'd);
 
*let today = %sysevalf('30Apr2013'd);
 +
%let today = %sysfunc(mdy(04,01,2013));
 
options nosource;
 
options nosource;
 
%put today: &today %sysfunc(putn(&today,weekdate29.));
 
%put today: &today %sysfunc(putn(&today,weekdate29.));
Line 101: Line 149:
 
* [[Date_datetime_time_stamp]]
 
* [[Date_datetime_time_stamp]]
  
== Macros ==
+
Note: these functions can be use in ODS filenames.
  
# Save these two macros in a folder 'C:\SAS-site\macros'
+
=== Syntax of Interval-Indexing function intnx ===
# add this code to your autoexec:
+
  
<source lang="sas">
+
The Interval-Indexing function intnx
Filename Project  '.';
+
saves the headaches of off-by-one errors
Libname  Library  '..\sas7b';
+
when calculating previous begin- and end-dates of intervals.
* macros: autocall: *.sas;
+
options  MautoSource
+
        SASautos = (Project 'C:\SAS-site\macros' SASautos);
+
</source>
+
  
The above code is from:
+
Notes:
[[Setting_Up_Project_Config_and_AutoExec]]
+
* 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
  
=== Macro Date-Report-Zero ===
+
<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">
 
<source lang="sas">
/*   name: DateRpt0.sas
+
*name: demo-mvar-basics.sas;
description: report with date-begin and -end
+
    purpose: template
+
    /******/
+
%MACRO daterpt0
+
      (data      =sashelp.class
+
      ,var        =age
+
      ,date_begin =11
+
      ,date_end  =13
+
      ,testing    =0
+
      );
+
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;
+
%mend daterpt0;
+
</source>
+
  
==== Macro Date-Report-Zero Tests ====
+
* assignment;
 +
%let today = %sysfunc(today());
 +
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
 +
%let interval = week;
 +
%let format = weekdate29.;
  
<source lang="sas">
+
* calculations;
/*    name: daterpt0-test.sas
+
%let D_Begin  =%sysfunc(intnx(&Interval,&today,0,begin));
description: examples
+
%let D_Middle =%sysfunc(intnx(&Interval,&today,0,middle));
    purpose: unit test
+
%let D_End    =%sysfunc(intnx(&Interval,&today,0,end  ));
    /******/
+
 
options mprint;
+
* display;
%daterpt0();
+
%put today: &today %sysfunc(putn(&today,worddate18.));
%daterpt0(data      =library.testdata
+
%put today: &today %sysfunc(putn(&today,&format));
        ,var        =date
+
%put D_Begin : &D_Begin %sysfunc(putn(&D_Begin ,&format));
        ,date_begin =117
+
%put D_Middle: &D_Begin %sysfunc(putn(&D_Middle,&format));
        ,date_end  =139
+
%put D_End  : &D_End  %sysfunc(putn(&D_End  ,&format));
        );
+
*previous week;
+
%Let today = %eval(3*366);
+
%daterpt0(data      =library.testdata
+
        ,var        =date
+
        ,date_begin =%sysfunc(intnx(week,&today,-1,begin))
+
        ,date_end  =%sysfunc(intnx(week,&today,-1,end  ))
+
        );
+
 
</source>
 
</source>
  
==== Macro Date-Report-Zero Demo Previous Any ====
+
 
 +
== Programs ==
 +
 
 +
=== Program Test-Data ===
  
 
<source lang="sas">
 
<source lang="sas">
  /*    name: daterpt0-demo-prev-any.sas
+
  /*    name: test-data
description: calling macro Date-Report-0
+
description: provide data with dates
    purpose: template
+
purpose   : for use by other demo programs
    /******/
+
usage:
options mprint;
+
%daterpt0(data = Library.TestData
%let today = %sysfunc(today());
+
        ,var  = date
%let today = %sysevalf('2Oct2012'd);
+
        ,...);
%let today = %eval(3*366);*test data range;
+
        /*******/
 +
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>
  
*choice: previous which?;
+
== Macros ==
%let interval = year;
+
%let interval = quarter;
+
%let interval = month;
+
%let interval = week;
+
  
%daterpt0(data      =library.testdata
+
# Save these two macros in a folder 'C:\SAS-site\macros'
        ,var        =date
+
# add this code to your autoexec:
        ,date_begin =%sysfunc(intnx(&interval.,&today,-1,begin))
+
 
         ,date_end  =%sysfunc(intnx(&interval.,&today,-1,end  ))
+
<source lang="sas">
        );
+
Filename Project  '.';
 +
Libname  Library  '..\sas7b';
 +
* macros: autocall: *.sas;
 +
options  MautoSource
 +
         SASautos = (Project 'C:\SAS-site\macros' SASautos);
 
</source>
 
</source>
  
=== Macro DateLoop ===
+
The above code is from:
 +
[[Setting_Up_Project_Config_and_AutoExec]]
  
This is.a macro function; it has parameters for the macro do loop: date-begin, -end and increment,
+
=== Macro DateLoop ===
and the date interval for the intnx function.
+
==== Macro DateLoop ====
  
 +
note: This is New and Improved, different from the macro in the paper.
  
 
<source lang="sas">
 
<source lang="sas">
 
  /*    name: ...\SAS-site\macros\dateloop.sas
 
  /*    name: ...\SAS-site\macros\dateloop.sas
     author: Ronald J. Fehd  2013   copied: 2/7/2013 4:07:59 PM
+
     author: Ronald J. Fehd  2013-March-28
description: macro function do loop from date-start to date-stop
+
description: macro function do loop from loop-start to loop-stop
 
purpose: standardize calling of report macros for many intervals
 
purpose: standardize calling of report macros for many intervals
NOTES: loop variable ThisDay is between D_Begin and D_End
+
NOTES: called macro must have parameters Begin and End
        i.e.: D_Begin <= ThisDay <= D_End
+
sas.help: Incrementing Dates and Times
      called macro must have parameters Date_Begin and Date_End
+
      loop-step and interval must agree
+
sas.help: Incremening Dates and Times
+
 
             by Using Multipliers and by Shifting Intervals
 
             by Using Multipliers and by Shifting Intervals
 
sas.wiki: http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
 
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
 
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
 
%Macro dateloop
         (loop_start = /* integer of date */
+
         (start      =0 /* integer of date */
         ,loop_stop  = /* integer of date */
+
         ,stop      =2 /* integer of date */
        ,loop_step  =1 /* greater than or equal to interval:
+
                          week:7 month:31 quarter:92 year:366 */
+
 
         ,interval  =day /* simple in (week month quarter year)
 
         ,interval  =day /* simple in (week month quarter year)
 
  /* intnx(interval: complex: week2==14 days week.2==Monday */
 
  /* intnx(interval: complex: week2==14 days week.2==Monday */
Line 219: Line 300:
 
         ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
 
         ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
 
         ,semicolon  =0
 
         ,semicolon  =0
 +
        ,MacroText  =.
 
         ,testing    =0
 
         ,testing    =0
        ,format    =weekdate29. /* putn(&mvar,&format) */
+
         )/des = 'site: calls reporting macro with dates'
         )/des = 'site: calls reporting macro with dates';
+
        /* ** store /* ** source /* */;
%local MacroCall ThisDay D_Begin D_End;
+
%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;
 
%if %scan(&MacroName,1) eq put %then %let Semicolon = 1;
 
%let Testing = %eval(  &Testing      or &Semicolon
 
%let Testing = %eval(  &Testing      or &Semicolon
                     or %sysfunc(getoption(mprint)) eq MPRINT);
+
                     or     %sysfunc(getoption(mprint )) eq MPRINT
%put &SysMacroName start: %sysfunc(putn(&loop_start,&format));
+
                        and %sysfunc(getoption(source2)) eq SOURCE2);
%put &SysMacroName  stop: %sysfunc(putn(&loop_stop ,&format));
+
%put &SysMacroName start: %sysfunc(putn(&start,&format));
 +
%put &SysMacroName  stop: %sysfunc(putn(&stop ,&format));
 +
%let D_Begin = %sysfunc(intnx(&Interval,&start,0,begin));
  
%do ThisDay = &Loop_Start %to &Loop_Stop %by &Loop_Step;
+
%do %until(&D_Begin gt &Stop);
     %let D_Begin  = %sysfunc(intnx(&Interval,&ThisDay,0,begin));
+
     %let D_End = %sysfunc(intnx(&Interval,&D_Begin,0,end  ));
     %let D_End    = %sysfunc(intnx(&Interval,&ThisDay,0,end ));
+
     %if "&MacroText" ne "." %then %do;
     %let MacroCall = &MacroName(;
+
        %local d dd ddd day day3;
    %if %length(&MacroParms) %then
+
        %let d    = %sysfunc(weekday(&D_begin));
        %let MacroCall = &MacroCall.%unquote(&MacroParms,);
+
        %let dd  = %sysfunc(putn(%sysfunc(day(&D_begin)),z2));
    %let MacroCall=&MacroCall.date_begin=&D_Begin,date_end=&D_End);
+
        %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;
 
     %if &Testing %then %do;
         %put &SysMacroName:begin %sysfunc(putn(&D_Begin,&format));
+
         %put &SysMacroName: begin %sysfunc(putn(&D_Begin,&format));
         %put &SysMacroName: end %sysfunc(putn(&D_End  ,&format));
+
         %put &SysMacroName:   end %sysfunc(putn(&D_End  ,&format));
        %put &SysMacroName: &MacroCall;
+
 
         %end;
 
         %end;
    %&MacroCall.
 
 
     %if &Semicolon %then %do;
 
     %if &Semicolon %then %do;
 
         ;
 
         ;
 
         %end;
 
         %end;
 +
    %let D_Begin = %eval(&D_End +1);
 
     %end;
 
     %end;
 
%mend dateloop;
 
%mend dateloop;
 
</source>
 
</source>
 +
 +
see also: [[Do_which_loop_until_or_while]]
  
 
==== Program DateLoop tests ====
 
==== Program DateLoop tests ====
Line 254: Line 365:
 
*name: dateloop-testing.sas;
 
*name: dateloop-testing.sas;
 
%let today = %sysfunc(today());
 
%let today = %sysfunc(today());
*let today = %sysevalf('2Oct2012'd);
+
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
  
%dateloop(loop_start =%sysfunc(intnx(quarter,&today,-1,sameday))
+
%dateloop(start    = %sysfunc(intnx(quarter,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(month  ,&today, 0,begin  ))
+
         ,stop    = %sysfunc(intnx(month  ,&today, 0,begin  ))
        ,loop_step  =31
+
         ,interval = month
         ,interval   =month
+
 
         );
 
         );
%dateloop(loop_start =%sysfunc(intnx(month,&today,-1,sameday))
+
%dateloop(start    = %sysfunc(intnx(month,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(week  ,&today, 0,begin  ))
+
         ,stop    = %sysfunc(intnx(week  ,&today, 0,begin  ))
        ,loop_step  =7
+
         ,interval = week
         ,interval   =week
+
 
         );
 
         );
%dateloop(loop_start =%sysfunc(intnx(week,&today,-1,sameday))
+
%dateloop(start    = %sysfunc(intnx(week,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(day ,&today, 0,begin  ))
+
         ,stop    = %sysfunc(intnx(day ,&today, 0,begin  ))
        ,loop_step  =1
+
         ,interval = day
         ,interval   =day
+
 
         );
 
         );
 
</source>
 
</source>
Line 280: Line 389:
 
DATELOOP:begin    Thursday, November 1, 2012
 
DATELOOP:begin    Thursday, November 1, 2012
 
DATELOOP:  end    Friday, November 30, 2012
 
DATELOOP:  end    Friday, November 30, 2012
DATELOOP: put note:(date_begin=19298,date_end=19327)
 
note:(date_begin=19298,date_end=19327)
 
  
 
DATELOOP:begin    Saturday, December 1, 2012
 
DATELOOP:begin    Saturday, December 1, 2012
 
DATELOOP:  end    Monday, December 31, 2012
 
DATELOOP:  end    Monday, December 31, 2012
DATELOOP: put note:(date_begin=19328,date_end=19358)
 
note:(date_begin=19328,date_end=19358)
 
  
 
DATELOOP:begin      Tuesday, January 1, 2013
 
DATELOOP:begin      Tuesday, January 1, 2013
 
DATELOOP:  end    Thursday, January 31, 2013
 
DATELOOP:  end    Thursday, January 31, 2013
DATELOOP: put note:(date_begin=19359,date_end=19389)
 
note:(date_begin=19359,date_end=19389)
 
 
....
 
....
 
</pre>
 
</pre>
Line 299: Line 402:
 
<source lang="sas">
 
<source lang="sas">
 
*name: dateloop-tests-integers.sas;
 
*name: dateloop-tests-integers.sas;
%dateloop(loop_start =0
+
%dateloop(start    = 0
         ,loop_stop  =7
+
         ,stop    = 7
        ,loop_step  =1
+
         ,interval = day
         ,interval   =day
+
 
         );
 
         );
%dateloop(loop_start =0
+
%dateloop(start    = 0
         ,loop_stop  =92
+
         ,stop    = 92
        ,loop_step  =31
+
         ,interval = month
         ,interval   =month
+
 
         );
 
         );
%dateloop(loop_start =0
+
%dateloop(start    = 0
         ,loop_stop  =366
+
         ,stop    = 366
        ,loop_step  =92
+
         ,interval = quarter
         ,interval   =quarter
+
 
         );
 
         );
%dateloop(loop_start =0
+
%dateloop(start    = 0
         ,loop_stop  =366
+
         ,stop    = 366
        ,loop_step  =31
+
         ,interval = month
         ,interval   =month
+
 
         );
 
         );
 
</source>
 
</source>
Line 324: Line 423:
 
<pre>
 
<pre>
 
1          *name: dateloop-tests-integers.sas;
 
1          *name: dateloop-tests-integers.sas;
2          %dateloop(loop_start =0
+
2          %dateloop(start    = 0
3                  ,loop_stop  =7
+
3                  ,stop    = 7
4                   ,loop_step  =1
+
4                  ,interval = day
5                   ,interval   =day
+
5                   );
6                   );
+
 
DATELOOP start:      Friday, January 1, 1960
 
DATELOOP start:      Friday, January 1, 1960
 
DATELOOP  stop:      Friday, January 8, 1960
 
DATELOOP  stop:      Friday, January 8, 1960
Line 338: Line 436:
 
DATELOOP:  end      Friday, January 8, 1960
 
DATELOOP:  end      Friday, January 8, 1960
  
7         %dateloop(loop_start =0
+
6         %dateloop(start  = 0
8                   ,loop_stop  =92
+
7                   ,stop    = 92
9                  ,loop_step  =31
+
8                 ,interval = month
10                 ,interval   =month
+
9                 );
11                 );
+
 
DATELOOP start:      Friday, January 1, 1960
 
DATELOOP start:      Friday, January 1, 1960
 
DATELOOP  stop:      Saturday, April 2, 1960
 
DATELOOP  stop:      Saturday, April 2, 1960
Line 361: Line 458:
 
     /******/
 
     /******/
 
%let today = %sysfunc(today());
 
%let today = %sysfunc(today());
%let today = %sysevalf('2Oct2012'd);
+
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('2Oct2012'd);
  
 
*note: start is -1 year;
 
*note: start is -1 year;
%dateloop(loop_start =%sysfunc(intnx(year ,&today,-1,sameday))
+
%dateloop(start    = %sysfunc(intnx(year ,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(month,&today, 0,begin  ))
+
         ,stop    = %sysfunc(intnx(month,&today, 0,begin  ))
        ,loop_step  =31
+
         ,interval = month
         ,interval   =month
+
 
         );
 
         );
 
*note: start is -12 months;
 
*note: start is -12 months;
%dateloop(loop_start =%sysfunc(intnx(month,&today,-12,sameday))
+
%dateloop(start    = %sysfunc(intnx(month,&today,-12,sameday))
         ,loop_stop  =%sysfunc(intnx(month,&today, 0 ,begin  ))
+
         ,stop    = %sysfunc(intnx(month,&today, 0 ,begin  ))
        ,loop_step  =31
+
         ,interval = month
         ,interval   =month
+
 
         );
 
         );
 
</source>
 
</source>
Line 384: Line 480:
 
     purpose: template
 
     purpose: template
 
to add for testing:
 
to add for testing:
        ,loop_step  = 7
 
 
         ,interval  = week
 
         ,interval  = week
        ,loop_step  = 31
 
 
         ,interval  = month
 
         ,interval  = month
 
         ,MacroName  = daterpt0
 
         ,MacroName  = daterpt0
Line 392: Line 486:
 
     /******/
 
     /******/
 
%let today = %sysfunc(today());
 
%let today = %sysfunc(today());
%let today = %sysevalf('2Oct2012'd);
+
*let today = %sysfunc(mdy(5,1,2013));
%let today = %eval(3*366);*test data range;
+
*let today = %sysevalf('01May2013'd);
  
 
*choice: previous which?;
 
*choice: previous which?;
Line 402: Line 496:
  
 
*report previous &interval by day;
 
*report previous &interval by day;
%dateloop(loop_start = %sysfunc(intnx(&interval.,&today,-1,begin))
+
%dateloop(start    = %sysfunc(intnx(&interval.,&today,-1,begin))
         ,loop_stop  = %sysfunc(intnx(&interval.,&today,-1,end  ))
+
         ,stop    = %sysfunc(intnx(&interval.,&today,-1,end  ))
        ,loop_step  = 1
+
         ,interval = day
         ,interval   = day
+
 
         );
 
         );
 
</source>
 
</source>
Line 418: Line 511:
 
     /******/
 
     /******/
 
%let today = %sysfunc(today());
 
%let today = %sysfunc(today());
%let today = %sysevalf('21Dec2012'd);
+
*let today = %sysfunc(mdy(5,1,2013));
 +
*let today = %sysevalf('01May2013'd);
  
 
%let interval = month;*quarter year;
 
%let interval = month;*quarter year;
Line 431: Line 525:
 
%let PrevDay  = %sysfunc(intnx(&interval,&today,-1,begin));
 
%let PrevDay  = %sysfunc(intnx(&interval,&today,-1,begin));
  
%dateloop(loop_start =%sysfunc(nwkdom(&week_of_month,&day_of_week
+
%dateloop(start    = %sysfunc(nwkdom(&week_of_month,&day_of_week
                                    ,%sysfunc(month(&PrevDay))
+
                                        ,%sysfunc(month(&PrevDay))
                                    ,%sysfunc(year (&PrevDay)) ))
+
                                        ,%sysfunc(year (&PrevDay)) ))
         ,loop_stop  =%sysfunc(intnx(&interval,&today,-1,end))
+
         ,stop    = %sysfunc(intnx(&interval,&today,-1,end))
        ,loop_step  =7
+
         ,interval = week.&day_of_week
         ,interval   =week.&day_of_week
+
 
         );
 
         );
 
</source>
 
</source>
Line 452: Line 545:
 
options mprint;
 
options mprint;
 
%let today = %sysfunc(today());
 
%let today = %sysfunc(today());
%let today = %sysevalf('2Oct2012'd);
+
*let today = %sysfunc(mdy(5,1,2013));
%let today = %eval(3*366);*test data range;
+
*let today = %sysevalf('01May2013'd);
  
 
%let interval = month;
 
%let interval = month;
Line 460: Line 553:
  
 
*report first (short) week report includes first of month;
 
*report first (short) week report includes first of month;
%dateloop(loop_start = &date_start
+
%dateloop(start    = &date_start
         ,loop_stop  = &date_stop
+
         ,stop    = &date_stop
        ,loop_step  = 7
+
         ,interval = week
         ,interval   = week
+
 
         );
 
         );
 
*reports begin on Saturday==day=7 for Monday: week.2;
 
*reports begin on Saturday==day=7 for Monday: week.2;
%dateloop(loop_start = &date_start
+
%dateloop(start    = &date_start
         ,loop_stop  = &date_stop
+
         ,stop    = &date_stop
        ,loop_step  = 7
+
         ,interval = week.7
         ,interval   = week.7
+
 
         );
 
         );
 
*weekly reports begin on first: ThisDay in (1 8 15 22 29);
 
*weekly reports begin on first: ThisDay in (1 8 15 22 29);
%dateloop(loop_start = &date_start
+
%dateloop(start    = &date_start
         ,loop_stop  = &date_stop
+
         ,stop    = &date_stop
        ,loop_step  = 7
+
         ,interval = week.%sysfunc(weekday(&date_start))
         ,interval   = week.%sysfunc(weekday(&date_start))
+
 
         );
 
         );
 
</source>
 
</source>
Line 481: Line 571:
 
<pre>
 
<pre>
 
17        *report first (short) week report includes first of month;
 
17        *report first (short) week report includes first of month;
18        %dateloop(loop_start = &date_start
+
18        %dateloop(start = &date_start
19                  ,loop_stop = &date_stop
+
19                  ,stop = &date_stop
20                  ,loop_step  = 7
+
 
21                  ,interval  = week
 
21                  ,interval  = week
 
22                  );
 
22                  );
Line 492: Line 581:
 
....
 
....
 
23        *reports begin on Saturday==day=7 for Monday: week.2;
 
23        *reports begin on Saturday==day=7 for Monday: week.2;
24        %dateloop(loop_start = &date_start
+
24        %dateloop(start = &date_start
25                  ,loop_stop = &date_stop
+
25                  ,stop = &date_stop
26                  ,loop_step  = 7
+
 
27                  ,interval  = week.7
 
27                  ,interval  = week.7
 
28                  );
 
28                  );
Line 503: Line 591:
 
....
 
....
 
29        *weekly reports begin on first: ThisDay in (1 8 15 22 29);
 
29        *weekly reports begin on first: ThisDay in (1 8 15 22 29);
30        %dateloop(loop_start = &date_start
+
30        %dateloop(start = &date_start
31                  ,loop_stop = &date_stop
+
31                  ,stop = &date_stop
32                  ,loop_step  = 7
+
 
33                  ,interval  = week.%sysfunc(weekday(&date_start))
 
33                  ,interval  = week.%sysfunc(weekday(&date_start))
 
34                  );
 
34                  );
Line 512: Line 599:
 
DATELOOP:begin      Tuesday, January 1, 2013
 
DATELOOP:begin      Tuesday, January 1, 2013
 
DATELOOP:  end      Monday, January 7, 2013
 
DATELOOP:  end      Monday, January 7, 2013
 
 
</pre>
 
</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 ==
Line 522: Line 887:
 
** Dating SAS® and MS Excel
 
** Dating SAS® and MS Excel
 
** http://www2.sas.com/proceedings/sugi29/068-29.pdf
 
** http://www2.sas.com/proceedings/sugi29/068-29.pdf
 
 
* Toby Dunn
 
* Toby Dunn
 
** Handling Dates in the Macro Facility
 
** Handling Dates in the Macro Facility
Line 532: Line 896:
 
** 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]]
 +
** [[Do_which_loop_until_or_while]]
 
** [[Macro_CallMacr]] is the predecessor of Macro DateLoop
 
** [[Macro_CallMacr]] is the predecessor of Macro DateLoop
 
** [[Macro_CallText]]
 
** [[Macro_CallText]]
Line 549: Line 915:
 
** Pretty Dates All in a Row
 
** 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 Schrier
+
* Howard Schreier
 
** [[Generating_Holiday_Lists]]
 
** [[Generating_Holiday_Lists]]
** nwkdom: N weekday of month :: find date of WDth weekday in Wth week of month
+
** nwkdom: Nth weekday of month :: find date of Nth weekday in Wth week of month
* Sarah Woodruff and Toby Dunn  
+
* Sarah Woodruff and Toby Dunn
 
** Understanding and Controlling Your Do-Loops
 
** Understanding and Controlling Your Do-Loops
 
** http://analytics.ncsu.edu/sesug/2010/FF01.Woodruff.pdf
 
** http://analytics.ncsu.edu/sesug/2010/FF01.Woodruff.pdf
Line 566: Line 932:
 
--[[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)