SAS Support
Self Service Support
Assisted Support
Premium Support
Enterprise Support
Logging a Call
Service Levels
Hints and Tips
 

MACRO TO GENERATE A LIST OF VARIABLES OR VALUES BASED ON DATES

SAS modules addressed: Base, Data Step/SQL

If you need to provide a list of variables that are based on dates such as Dyyyymm, e.g. D200711 D200712 D200801. This can be a problem to do easily with macro loops as the values may not be contiguous; here is a basic utility macro to generate a required list of values. It uses the INTCK function to generate the date list. The macro needs to be stored in an AUTOCALL library or submitted in your current SAS session.

The macro has 9 parameters; the required positional parameters are the following and must be specified in the order below.

  • start Date to start with, e.g. 01JAN2007
  • stop Date to stop at, e.g. 31DEC2009
  • interval Interval to increment by, e.g. MONTH
  • format Format to use for the returned value(s), e.g. MONYY5.

Optional keyword paramaters

  • informat= Defaults to DATE9 - The INFORMAT to use to convert the START/STOP values to SAS Date values
  • alignment= Defaults to BEGINNING - The alignment value to use with INTNX
  • prefix= No default - string to place immediately before each returned value
  • suffix= No default - string to place immediately after each returned value
  • seperator= No default - string to place between the returned values

NB: If special characters are to be passed, e.g. double quote or commas, then you need to ensure that you use macro quoting, e.g. 


%DATELOOP(01JAN2007,31DEC2009,MONTH,DATE9.,INFORMAT=DATE9.,ALIGNMENT=

BEGINNING,PREFIX=%BQUOTE("),SUFFIX=%BQUOTE("),SEPERATOR=%BQUOTE(,)) ;

The macro should be callable from any context, as it just uses macro code, but invalid parameters will generate errors.


* Define the macro ;

%macro DateLoop(start,stop,interval,format,informat=date9.,alignment=beginning,prefix=,suffix=,seperator=) ;

   %local increment finnish;

   %let finnish = %sysfunc(intck(&interval,%sysfunc(inputn(&start,&informat)),%sysfunc(inputn(&stop,&informat)))) ;

   %do increment = 0 %to &finnish  ; 

      %do ;

%unquote(&prefix)%sysfunc(intnx(&interval,%sysfunc(inputn(&start,&informat)),&increment,&alignment),&format)%unquote(&suffix)

      %end ;

      %if &seperator ne %then 

          %if &increment ne &finnish %then 

            %do ; 

%unquote(&seperator)

            %end ;

   %end ;

%mend DateLoop ;



* Create some sample data to work with;



proc transpose data=sashelp.air out=work.air1(drop=_name_ _label_) ; var air ; id date ; run ;

data work.air2 ; set sashelp.air ; datevar = put(date,monyy5.) ; run ;



* Creates a KEEP list ;

data work.example1 ; * Generates NOV49 DEC49 JAN50 FEB50 ;

   set work.air1(keep=%DateLoop(01NOV1949,01FEB1950,MONTH,MONYY5.)) ;

run ;

* Creates a KEEP list ;

proc sql ; * Generates "NOV49","DEC49","JAN50","FEB50" ;

   create table work.example2 as 

      select *

        from work.air2 

       where datevar in (%DateLoop(NOV49,FEB50,MONTH,MONYY5.,informat=MONYY5.,

                                   prefix=%bquote("),suffix=%bquote("),seperator=%bquote(,))) ;

quit ;



* Other examples used during testing... ;

%put %DateLoop(0702,0702,month,yymmn4.,informat=yymmn4.) ;

%put %DateLoop(0709,0802,month,yymmn4.,informat=yymmn4.) ;

%put %DateLoop(01SEP2009,01FEB2010,qtr,YYQ6,informat=date9.) ;



%put %DateLoop(01SEP2009,01FEB2010,month,date9.,informat=date9.,alignment=end) ;

%put %DateLoop(01SEP2009,01FEB2010,month,yymmn6.,informat=date9.,prefix=Y) ; 

%put %DateLoop(01SEP2009,01FEB2010,month,yymmn6.,informat=date9.,prefix=M,seperator=%quote(,)) ;

Did you find this page useful?

If you have any comments or questions, feel free to contact us.



0845 402 9907