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.


Macro Loops with Dates

From sasCommunity
Revision as of 17:16, 12 February 2013 by Ron.Fehd.macro.maven (Talk | contribs)

Jump to: navigation, search

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)