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
(Programs: macro do-dates and do-dates-tests)
(MAJOR off-line rewrite page.reads.to.date: 419)
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]]
 +
 +
This paper is accepted for SGF2013.QT: Quick Tips, formerly known as Coders Corner.
 +
 +
Audience: macro programmers
  
 
== Overview ==
 
== Overview ==
 +
 +
This page contains the programs, macros, tests, and calling programs
 +
in the paper
  
 
== Programs ==
 
== Programs ==
  
=== Program Info-from-Dates-from-Mvars ===
+
=== 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 3*370;
 +
  do EntityId = int(Date/17);
 +
      do Value = int(Date**3/19);
 +
        output;
 +
        end;
 +
      end;
 +
  end;
 +
stop;
 +
run;</source>
 +
 
 +
=== Program Info-in-Dates ===
  
 
<source lang="sas">
 
<source lang="sas">
  /*    name: Info-from-dates-from-mvars
+
  /*    name: info-in-dates.sas
description: provide list of system mvars with dates
+
description: provide associative array of values from dates
purpose    : show how to convert dates to integers
+
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
other formats with year2:
+
            Understanding How SAS Handles Dates
%put today: &today %sysfunc(putn(&today,date7.));
+
reference:
%put today: &today %sysfunc(putn(&today,mmddyy10.));
+
  http://www.sascommunity.org/wiki/Date_datetime_time_stamp
 
*******/
 
*******/
%put sysdate: &Sysdate.;
+
%let today = 0;
%put sysdate9: &Sysdate9.;
+
%let today = 366;
 
+
*let today = %sysfunc(today());
%let today = %sysevalf('10JAN2013'd);
+
*let today = %sysevalf('30Apr2013'd);
%let today = %sysevalf('10Feb2013'd);
+
options nosource;
%let today = %sysevalf('21Dec2012'd);
+
*let today = %sysevalf("&Sysdate9."d);
+
*let today = %sysfunc(today()) ;
+
*let today = %sysfunc(date()) ;
+
 
+
%put today: &today %sysfunc(putn(&today,date9.));
+
%put today: &today %sysfunc(putn(&today,mmddyy10.));
+
%put today: &today %sysfunc(putn(&today,worddate18.));
+
 
%put today: &today %sysfunc(putn(&today,weekdate29.));
 
%put today: &today %sysfunc(putn(&today,weekdate29.));
*weekdate29 is the datetime-stamp in title1, update by setting option dtreset;
 
  
*parts:;
+
%put day/month.N  :%sysfunc(day(&today));
%put day/month: &today %sysfunc(day(&today));
+
%put day/month.z2 :%sysfunc(putn(%sysfunc(day(&today)),z2));
%put month: &today %sysfunc(month(&today));
+
%put day/week.N  :%sysfunc(weekday(&today));
%put year: &today %sysfunc(year(&today));
+
%put day/week.name:%sysfunc(putn(&today,downame));
 +
%put day/year     :%substr(%sysfunc(juldate7(&today)),5,3);
  
%put week/year: &today %sysfunc(week(&today,u));
+
%put month.N    :%sysfunc(month(&today));
%put week/year: &today %sysfunc(week(&today,v));
+
%put month.z2  :%sysfunc(putn(%sysfunc(month(&today)),z2));
%put week/year: &today %sysfunc(week(&today,w));
+
%put month.name :%sysfunc(putn(&today,monname));
 +
%put month.name3:%sysfunc(putn(&today,monname3));
 +
%put year       :%sysfunc(year(&today));
  
%put day/year: &today %substr(%sysfunc(juldate7(&today)),5,3);
+
%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>
 
</source>
  
 
log:
 
log:
 
<pre>
 
<pre>
14         %put sysdate9: &Sysdate9.;
+
17         options nosource;
sysdate9: 15JAN2013
+
today: 366      Sunday, January 1, 1961
15       
+
day/month.: 1
18        %let today = %sysevalf('21Dec2012'd);
+
day/month.z2 : 01
 
+
day/week.N  : 1
23        %put today: &today %sysfunc(putn(&today,date9.));
+
day/week.name:   Sunday
today: 19348 21DEC2012
+
day/year    : 001
24        %put today: &today %sysfunc(putn(&today,mmddyy10.));
+
month.N    : 1
today: 19348 12/21/2012
+
month.z2  : 01
25        %put today: &today %sysfunc(putn(&today,worddate18.));
+
month.name :   January
today: 19348  December 21, 2012
+
month.name3: Jan
26        %put today: &today %sysfunc(putn(&today,weekdate29.));
+
year       : 1961
today: 19348    Friday, December 21, 2012
+
week/year.u: 1
27       
+
week/year.v: 52
28        *parts:;
+
week/year.w: 0
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>
  
=== Macro Do-Dates ===
+
see also:
This macro has parameters for the macro do loop: date-begin, -end and increment,
+
 
 +
* [[Date_datetime_time_stamp]]
 +
 
 +
== 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 Date-Report-Zero ===
 +
 
 +
<source lang="sas">
 +
/*    name: DateRpt0.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 ====
 +
 
 +
<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 = %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>
 +
 
 +
==== 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 = %sysevalf('2Oct2012'd);
 +
%let today = %eval(3*366);*test data range;
 +
 
 +
*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 DateLoop ===
 +
 
 +
This is.a macro function; it has parameters for the macro do loop: date-begin, -end and increment,
 
and the date interval for the intnx function.
 
and the date interval for the intnx function.
 +
  
 
<source lang="sas">
 
<source lang="sas">
  /*    name: do_dates
+
  /*    name: ...\SAS-site\macros\dateloop.sas
  location: ...\SAS-site\macros
+
     author: Ronald J. Fehd  2013   copied: 2/7/2013 4:07:59 PM
     author: Ronald J. Fehd  2013
+
description: macro function do loop from date-start to date-stop
description: macro do loop from date-begin to date-end
+
purpose: standardize calling of report macros for many intervals
purpose   : standardize calling of other macros for
+
NOTES: loop variable ThisDay is between D_Begin and D_End
sas.help   : Incrementing Dates and Times
+
        i.e.: D_Begin <= ThisDay <= D_End
              by Using Multipliers and by Shifting Intervals
+
      called macro must have parameters Date_Begin and Date_End
sas.wiki   : http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
+
      loop-step and interval must agree
 +
sas.help: Incremening Dates and Times
 +
            by Using Multipliers and by Shifting Intervals
 +
sas.wiki: http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
 
predecessor: http://www.sascommunity.org/wiki/Macro_CallMacr
 
predecessor: http://www.sascommunity.org/wiki/Macro_CallMacr
******/
+
***********/
%Macro do_dates
+
%Macro dateloop
         (date_begin = /* integer or date literal */
+
         (loop_start = /* integer of date */
         ,date_end  = /* integer or date literal */
+
         ,loop_stop  = /* integer of date */
         ,increment =1   /* greater than or equal to interval:
+
         ,loop_step =1 /* greater than or equal to interval:
                            week:7 month:31 quarter:91 year:366 */
+
                          week:7 month:31 quarter:92 year:366 */
         ,interval  =day /* week month quarter etc */
+
         ,interval  =day /* simple in (week month quarter year)
         ,MacroName  =put note
+
/* intnx(interval: complex: week2==14 days week.2==Monday */
 +
         ,MacroName  =put note:
 
         ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
 
         ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
 
         ,semicolon  =0
 
         ,semicolon  =0
 
         ,testing    =0
 
         ,testing    =0
         );
+
         ,format    =weekdate29. /* putn(&mvar,&format) */
%let Testing = %eval(&Testing
+
        )/des = 'site: calls reporting macro with dates';
 +
%local MacroCall ThisDay D_Begin D_End;
 +
%if %scan(&MacroName,1) eq put %then %let Semicolon = 1;
 +
%let Testing = %eval(   &Testing     or &Semicolon
 
                     or %sysfunc(getoption(mprint)) eq MPRINT);
 
                     or %sysfunc(getoption(mprint)) eq MPRINT);
%if %scan(&MacroName,1) eq put %then %do;
+
%put &SysMacroName start: %sysfunc(putn(&loop_start,&format));
    %let Semicolon = 1;
+
%put &SysMacroName  stop: %sysfunc(putn(&loop_stop ,&format));
    %let Testing  = 1;
+
    %end;
+
%local MacroCall ThisDay;
+
  
%do ThisDay = &Date_Begin %to &Date_End %by &Increment;
+
%do ThisDay = &Loop_Start %to &Loop_Stop %by &Loop_Step;
     %if &Testing %then %put
+
     %let D_Begin  = %sysfunc(intnx(&Interval,&ThisDay,0,begin));
        note: ThisDay: &ThisDay %sysfunc(putn(&ThisDay,weekdate29.));
+
    %let D_End    = %sysfunc(intnx(&Interval,&ThisDay,0,end  ));
 
     %let MacroCall = &MacroName(;
 
     %let MacroCall = &MacroName(;
 
     %if %length(&MacroParms) %then
 
     %if %length(&MacroParms) %then
 
         %let MacroCall = &MacroCall.%unquote(&MacroParms,);
 
         %let MacroCall = &MacroCall.%unquote(&MacroParms,);
     %let MacroCall =
+
     %let MacroCall=&MacroCall.date_begin=&D_Begin,date_end=&D_End);
    &MacroCall.Date_begin=%sysfunc(intnx(&Interval,&ThisDay,0,begin));
+
    %if &Testing %then %do;
    %let MacroCall =
+
        %put &SysMacroName:begin %sysfunc(putn(&D_Begin,&format));
      &MacroCall.,Date_end=%sysfunc(intnx(&Interval,&ThisDay,0,end)));
+
        %put &SysMacroName:  end %sysfunc(putn(&D_End  ,&format));
 +
        %put &SysMacroName: &MacroCall;
 +
        %end;
 
     %&MacroCall.
 
     %&MacroCall.
 
     %if &Semicolon %then %do;
 
     %if &Semicolon %then %do;
Line 131: Line 246:
 
         %end;
 
         %end;
 
     %end;
 
     %end;
%mend do_dates;
+
%mend dateloop;
 
</source>
 
</source>
  
=== Program Do-Dates-tests ===
+
==== Program DateLoop tests ====
 +
 
 
<source lang="sas">
 
<source lang="sas">
options mprint;
+
*name: dateloop-testing.sas;
%do_dates(date_begin =0
+
%let today = %sysfunc(today());
         ,date_end   =7
+
*let today = %sysevalf('2Oct2012'd);
 +
 
 +
%dateloop(loop_start =%sysfunc(intnx(quarter,&today,-1,sameday))
 +
        ,loop_stop  =%sysfunc(intnx(month  ,&today, 0,begin  ))
 +
         ,loop_step  =31
 +
        ,interval   =month
 +
        );
 +
%dateloop(loop_start =%sysfunc(intnx(month,&today,-1,sameday))
 +
        ,loop_stop  =%sysfunc(intnx(week  ,&today, 0,begin  ))
 +
        ,loop_step  =7
 +
        ,interval  =week
 +
        );
 +
%dateloop(loop_start =%sysfunc(intnx(week,&today,-1,sameday))
 +
        ,loop_stop  =%sysfunc(intnx(day ,&today, 0,begin  ))
 +
        ,loop_step  =1
 +
        ,interval  =day
 +
        );
 +
</source>
 +
 
 +
log:
 +
<pre>
 +
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: put note:(date_begin=19298,date_end=19327)
 +
note:(date_begin=19298,date_end=19327)
 +
 
 +
DATELOOP:begin    Saturday, December 1, 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:  end    Thursday, January 31, 2013
 +
DATELOOP: put note:(date_begin=19359,date_end=19389)
 +
note:(date_begin=19359,date_end=19389)
 +
....
 +
</pre>
 +
 
 +
==== Program DateLoop tests integers ====
 +
 
 +
<source lang="sas">
 +
*name: dateloop-tests-integers.sas;
 +
%dateloop(loop_start =0
 +
        ,loop_stop  =7
 +
        ,loop_step  =1
 
         ,interval  =day
 
         ,interval  =day
        ,increment  =1
 
 
         );
 
         );
%do_dates(date_begin =0
+
%dateloop(loop_start =0
         ,date_end  =92
+
         ,loop_stop  =92
 +
        ,loop_step  =31
 
         ,interval  =month
 
         ,interval  =month
        ,increment  =31
 
 
         );
 
         );
%do_dates(date_begin =0
+
%dateloop(loop_start =0
         ,date_end  =366
+
         ,loop_stop  =366
 +
        ,loop_step  =92
 
         ,interval  =quarter
 
         ,interval  =quarter
        ,increment  =92
 
 
         );
 
         );
%do_dates(date_begin =0
+
%dateloop(loop_start =0
         ,date_end  =366
+
         ,loop_stop  =366
 +
        ,loop_step  =31
 
         ,interval  =month
 
         ,interval  =month
        ,increment  =31
 
 
         );
 
         );
 
</source>
 
</source>
Line 161: Line 323:
 
log:
 
log:
 
<pre>
 
<pre>
2          %do_dates(date_begin =0
+
1          *name: dateloop-tests-integers.sas;
3                  ,date_end  =7
+
2          %dateloop(loop_start =0
4                  ,interval  =day
+
3                  ,loop_stop  =7
5                  ,increment  =1
+
4                  ,loop_step  =1
 +
5                  ,interval  =day
 
6                  );
 
6                  );
note: ThisDay: 0       Friday, January 1, 1960
+
DATELOOP start:       Friday, January 1, 1960
note(Date_begin=0,Date_end=0)
+
DATELOOP  stop:       Friday, January 8, 1960
note: ThisDay: 1     Saturday, January 2, 1960
+
 
note(Date_begin=1,Date_end=1)
+
DATELOOP:begin       Friday, January 1, 1960
note: ThisDay: 2      Sunday, January 3, 1960
+
DATELOOP:  end      Friday, January 1, 1960
note(Date_begin=2,Date_end=2)
+
....
note: ThisDay: 3      Monday, January 4, 1960
+
DATELOOP:begin      Friday, January 8, 1960
note(Date_begin=3,Date_end=3)
+
DATELOOP:  end      Friday, January 8, 1960
note: ThisDay: 4      Tuesday, January 5, 1960
+
 
note(Date_begin=4,Date_end=4)
+
7          %dateloop(loop_start =0
note: ThisDay: 5   Wednesday, January 6, 1960
+
8                  ,loop_stop  =92
note(Date_begin=5,Date_end=5)
+
9                  ,loop_step  =31
note: ThisDay: 6     Thursday, January 7, 1960
+
10                  ,interval  =month
note(Date_begin=6,Date_end=6)
+
11                  );
note: ThisDay: 7      Friday, January 8, 1960
+
DATELOOP start:       Friday, January 1, 1960
note(Date_begin=7,Date_end=7)
+
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 = %sysevalf('2Oct2012'd);
 +
 
 +
*note: start is -1 year;
 +
%dateloop(loop_start =%sysfunc(intnx(year ,&today,-1,sameday))
 +
        ,loop_stop  =%sysfunc(intnx(month,&today, 0,begin  ))
 +
        ,loop_step  =31
 +
        ,interval  =month
 +
        );
 +
*note: start is -12 months;
 +
%dateloop(loop_start =%sysfunc(intnx(month,&today,-12,sameday))
 +
        ,loop_stop  =%sysfunc(intnx(month,&today, 0 ,begin  ))
 +
        ,loop_step  =31
 +
        ,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:
 +
        ,loop_step  = 7
 +
        ,interval  = week
 +
        ,loop_step  = 31
 +
        ,interval  = month
 +
        ,MacroName  = daterpt0
 +
        ,MacroParms = %nrstr(data=library.testdata,var=date)
 +
    /******/
 +
%let today = %sysfunc(today());
 +
%let today = %sysevalf('2Oct2012'd);
 +
%let today = %eval(3*366);*test data range;
 +
 
 +
*choice: previous which?;
 +
%let interval = year;
 +
%let interval = quarter;
 +
%let interval = month;
 +
%let interval = week;
 +
 
 +
*report previous &interval by day;
 +
%dateloop(loop_start = %sysfunc(intnx(&interval.,&today,-1,begin))
 +
        ,loop_stop  = %sysfunc(intnx(&interval.,&today,-1,end  ))
 +
        ,loop_step  = 1
 +
        ,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 = %sysevalf('21Dec2012'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(loop_start =%sysfunc(nwkdom(&week_of_month,&day_of_week
 +
                                    ,%sysfunc(month(&PrevDay))
 +
                                    ,%sysfunc(year (&PrevDay)) ))
 +
        ,loop_stop  =%sysfunc(intnx(&interval,&today,-1,end))
 +
        ,loop_step  =7
 +
        ,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 = %sysevalf('2Oct2012'd);
 +
%let today = %eval(3*366);*test data range;
 +
 
 +
%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(loop_start = &date_start
 +
        ,loop_stop  = &date_stop
 +
        ,loop_step  = 7
 +
        ,interval  = week
 +
        );
 +
*reports begin on Saturday==day=7 for Monday: week.2;
 +
%dateloop(loop_start = &date_start
 +
        ,loop_stop  = &date_stop
 +
        ,loop_step  = 7
 +
        ,interval  = week.7
 +
        );
 +
*weekly reports begin on first: ThisDay in (1 8 15 22 29);
 +
%dateloop(loop_start = &date_start
 +
        ,loop_stop  = &date_stop
 +
        ,loop_step  = 7
 +
        ,interval  = week.%sysfunc(weekday(&date_start))
 +
        );
 +
</source>
 +
log:
 +
<pre>
 +
17        *report first (short) week report includes first of month;
 +
18        %dateloop(loop_start = &date_start
 +
19                  ,loop_stop  = &date_stop
 +
20                  ,loop_step  = 7
 +
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(loop_start = &date_start
 +
25                  ,loop_stop  = &date_stop
 +
26                  ,loop_step  = 7
 +
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(loop_start = &date_start
 +
31                  ,loop_stop  = &date_stop
 +
32                  ,loop_step  = 7
 +
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>
 
</pre>
  
 
== References ==
 
== References ==
  
* Function nwkdom: N weekday of month :: find date of WDth weekday in Wth week of month
+
* Toby Dunn
** [[Generating_Holiday_Lists]]
+
** Handling Dates in the Macro Facility
* Handling Dates in the Macro Facility
+
*** http://analytics.ncsu.edu/sesug/2006/SC11_06.PDF
** Toby Dunn
+
*** http://www.nesug.org/proceedings/nesug06/cc/cc21.pdf
** http://analytics.ncsu.edu/sesug/2006/SC11_06.PDF
+
*** http://analytics.ncsu.edu/sesug/2009/FF004.Dunn.pdf
** http://www.nesug.org/proceedings/nesug06/cc/cc21.pdf
+
* Toby Dunn and Sarah Woodruff
** http://analytics.ncsu.edu/sesug/2009/FF004.Dunn.pdf
+
** It’s Five O’Clock Somewhere!!! Handling Dates And Times In SAS®
* It’s Five O’Clock Somewhere!!! Handling Dates And Times In SAS®
+
** Toby Dunn and Sarah Woodruff
+
 
** 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]]
+
** [[Macro_Do-Loop]]
** [[Macro_Clock]]
+
** [[Macro_Do_Loop_Continue_or_Leave]]
 
** [[Macro_TextLine]]
 
** [[Macro_TextLine]]
** [[Macro_Used_Real_Time]]
 
 
** [[Option_Dtreset]] for updating datetime-stamp in title1
 
** [[Option_Dtreset]] for updating datetime-stamp in title1
** [[Using_Sysfunc_to_date-stamp_data_set_names]]
+
* Wayne Finley
* Pretty Dates All in a Row  
+
** A Beginners Guide to SAS® Date and Time Handling
** Dianne Louise Rhodes
+
*** 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 Schrier
 +
** [[Generating_Holiday_Lists]]
 +
** nwkdom: N weekday of month :: find date of WDth weekday in Wth week of month
 +
 +
=== 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:Macros_by_Ron_Fehd]]
 
[[Category:Macros_by_Ron_Fehd]]

Revision as of 17:03, 7 February 2013

Writing Macro Loops with Dates from Then to Now

Author: Ronald_J._Fehd macro maven

This paper is accepted for SGF2013.QT: Quick Tips, formerly known as Coders Corner.

Audience: macro programmers

Overview

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

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 3*370;
   do EntityId = int(Date/17);
      do Value = int(Date**3/19);
         output;
         end;
      end;
   end;
stop;
run;

Program Info-in-Dates

 /*    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);
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:

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 Date-Report-Zero

 /*    name: DateRpt0.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;

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 = %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  ))
         );

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 = %sysevalf('2Oct2012'd);
%let today = %eval(3*366);*test data range;
 
*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 DateLoop

This is.a macro function; it has parameters for the macro do loop: date-begin, -end and increment, and the date interval for the intnx function.


 /*    name: ...\SAS-site\macros\dateloop.sas
     author: Ronald J. Fehd  2013    copied: 2/7/2013 4:07:59 PM
description: macro function do loop from date-start to date-stop
purpose: standardize calling of report macros for many intervals
NOTES: loop variable ThisDay is between D_Begin and D_End
         i.e.: D_Begin <= ThisDay <= D_End
       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
sas.wiki: http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
predecessor: http://www.sascommunity.org/wiki/Macro_CallMacr
***********/
%Macro dateloop
         (loop_start =  /* integer of date */
         ,loop_stop  =  /* 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)
 /* intnx(interval: complex: week2==14 days week.2==Monday */
         ,MacroName  =put note:
         ,MacroParms = /*%nrstr(data=sashelp.class,var=sex)*/
         ,semicolon  =0
         ,testing    =0
         ,format     =weekdate29. /* putn(&mvar,&format) */
         )/des = 'site: calls reporting macro with dates';
%local MacroCall ThisDay D_Begin D_End;
%if %scan(&MacroName,1) eq put %then %let Semicolon = 1;
%let Testing = %eval(   &Testing      or &Semicolon
                     or %sysfunc(getoption(mprint)) eq MPRINT);
%put &SysMacroName start: %sysfunc(putn(&loop_start,&format));
%put &SysMacroName  stop: %sysfunc(putn(&loop_stop ,&format));
 
%do ThisDay = &Loop_Start %to &Loop_Stop %by &Loop_Step;
    %let D_Begin   = %sysfunc(intnx(&Interval,&ThisDay,0,begin));
    %let D_End     = %sysfunc(intnx(&Interval,&ThisDay,0,end  ));
    %let MacroCall = &MacroName(;
    %if %length(&MacroParms) %then
        %let MacroCall = &MacroCall.%unquote(&MacroParms,);
    %let MacroCall=&MacroCall.date_begin=&D_Begin,date_end=&D_End);
    %if &Testing %then %do;
        %put &SysMacroName:begin %sysfunc(putn(&D_Begin,&format));
        %put &SysMacroName:  end %sysfunc(putn(&D_End  ,&format));
        %put &SysMacroName: &MacroCall;
        %end;
    %&MacroCall.
    %if &Semicolon %then %do;
        ;
        %end;
    %end;
%mend dateloop;

Program DateLoop tests

*name: dateloop-testing.sas;
%let today = %sysfunc(today());
*let today = %sysevalf('2Oct2012'd);
 
%dateloop(loop_start =%sysfunc(intnx(quarter,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(month  ,&today, 0,begin  ))
         ,loop_step  =31
         ,interval   =month
         );
%dateloop(loop_start =%sysfunc(intnx(month,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(week  ,&today, 0,begin  ))
         ,loop_step  =7
         ,interval   =week
         );
%dateloop(loop_start =%sysfunc(intnx(week,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(day ,&today, 0,begin  ))
         ,loop_step  =1
         ,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: put note:(date_begin=19298,date_end=19327)
note:(date_begin=19298,date_end=19327)

DATELOOP:begin    Saturday, December 1, 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:  end    Thursday, January 31, 2013
DATELOOP: put note:(date_begin=19359,date_end=19389)
note:(date_begin=19359,date_end=19389)
....

Program DateLoop tests integers

*name: dateloop-tests-integers.sas;
%dateloop(loop_start =0
         ,loop_stop  =7
         ,loop_step  =1
         ,interval   =day
         );
%dateloop(loop_start =0
         ,loop_stop  =92
         ,loop_step  =31
         ,interval   =month
         );
%dateloop(loop_start =0
         ,loop_stop  =366
         ,loop_step  =92
         ,interval   =quarter
         );
%dateloop(loop_start =0
         ,loop_stop  =366
         ,loop_step  =31
         ,interval   =month
         );

log:

1          *name: dateloop-tests-integers.sas;
2          %dateloop(loop_start =0
3                   ,loop_stop  =7
4                   ,loop_step  =1
5                   ,interval   =day
6                   );
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

7          %dateloop(loop_start =0
8                   ,loop_stop  =92
9                   ,loop_step  =31
10                  ,interval   =month
11                  );
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 = %sysevalf('2Oct2012'd);
 
*note: start is -1 year;
%dateloop(loop_start =%sysfunc(intnx(year ,&today,-1,sameday))
         ,loop_stop  =%sysfunc(intnx(month,&today, 0,begin  ))
         ,loop_step  =31
         ,interval   =month
         );
*note: start is -12 months;
%dateloop(loop_start =%sysfunc(intnx(month,&today,-12,sameday))
         ,loop_stop  =%sysfunc(intnx(month,&today, 0 ,begin  ))
         ,loop_step  =31
         ,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:
         ,loop_step  = 7
         ,interval   = week
         ,loop_step  = 31
         ,interval   = month
         ,MacroName  = daterpt0
         ,MacroParms = %nrstr(data=library.testdata,var=date)
    /******/
%let today = %sysfunc(today());
%let today = %sysevalf('2Oct2012'd);
%let today = %eval(3*366);*test data range;
 
*choice: previous which?;
%let interval = year;
%let interval = quarter;
%let interval = month;
%let interval = week;
 
*report previous &interval by day;
%dateloop(loop_start = %sysfunc(intnx(&interval.,&today,-1,begin))
         ,loop_stop  = %sysfunc(intnx(&interval.,&today,-1,end  ))
         ,loop_step  = 1
         ,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 = %sysevalf('21Dec2012'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(loop_start =%sysfunc(nwkdom(&week_of_month,&day_of_week
                                     ,%sysfunc(month(&PrevDay))
                                     ,%sysfunc(year (&PrevDay)) ))
         ,loop_stop  =%sysfunc(intnx(&interval,&today,-1,end))
         ,loop_step  =7
         ,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 = %sysevalf('2Oct2012'd);
%let today = %eval(3*366);*test data range;
 
%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(loop_start = &date_start
         ,loop_stop  = &date_stop
         ,loop_step  = 7
         ,interval   = week
         );
*reports begin on Saturday==day=7 for Monday: week.2;
%dateloop(loop_start = &date_start
         ,loop_stop  = &date_stop
         ,loop_step  = 7
         ,interval   = week.7
         );
*weekly reports begin on first: ThisDay in (1 8 15 22 29);
%dateloop(loop_start = &date_start
         ,loop_stop  = &date_stop
         ,loop_step  = 7
         ,interval   = week.%sysfunc(weekday(&date_start))
         );

log:

17         *report first (short) week report includes first of month;
18         %dateloop(loop_start = &date_start
19                  ,loop_stop  = &date_stop
20                  ,loop_step  = 7
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(loop_start = &date_start
25                  ,loop_stop  = &date_stop
26                  ,loop_step  = 7
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(loop_start = &date_start
31                  ,loop_stop  = &date_stop
32                  ,loop_step  = 7
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

References

References for working with Time

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