Date:         Wed, 7 Oct 2009 12:06:38 +0200
Reply-To:     Daniel Fernández <fdezdan@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Daniel Fernández <fdezdan@GMAIL.COM>
Subject:      An utility macro about dates.
Content-Type: text/plain; charset=ISO-8859-1

hi all,

I have being coding this simple utility macro that lists
all elements since a initial year and initial month (as char type)
to current year and month,in this way I could get automating maintenance
for my periodic programs which  use often that sort of filter statements like:

IF A in (200811,200812,200901,200902,200903,...200909);
or
where A in('200811','200812','200901','200902','200903',...,'200909');

Notice IF statement is insensitive to char or num type, but as WHERE
statement does (it is sensitive) I avoid possible errors if
this macro was used in a PROC SQL.


First I began coding this even simpler macro that only lists years
from initial year to current year:

%macro yrs(ini_year);
%do i=&ini_year  %to %sysfunc(year("&sysdate"d));
%sysfunc(quote(&i))
%end;
%mend;

Example (executing before 12-31-2009 nor 2010 neither 2011 will appear
at the ouput):

data have_a;
input a $;
cards;
2008
2009
2010
2011
;
run;

*WHERE statement (Notice that WHERE statement lists
their elements in the LOG);
proc sql;
select a from have_a
where a in (%yrs(2003));
quit;

* IF statement;
data need_a;
set have_a;
if a in (%yrs(2009));
run;

But when I tried to add the months so then every new month I have to
execute my programs for monthly reporting and periodic tasks is updated,
my macro code is so large like this:

%macro yrmonth(ini_year,ini_month);
%do i=&ini_year %to %sysfunc(year("&sysdate"d));

%if &i EQ &ini_year %then %do;
%do months=&ini_month %to 12;
%let entire= %sysfunc(abs(%sysfunc(mdy(&months,01,&i))),mmddyy10.);
%let com=%sysfunc(cats(
(%sysfunc(abs(%sysfunc(scan(&entire,3,'/'))),4.)),
(%sysfunc(abs(%sysfunc(scan(&entire,1,'/'))),z2.),$2.)
),$6.);
%sysfunc(quote(&com))
%end; %end;


%else %if &I < %sysfunc(year("&sysdate"d)) %then %do;
%do months=01 %to 12;
%let entire= %sysfunc(abs(%sysfunc(mdy(&months,01,&i))),mmddyy10.);
%let com=%sysfunc(cats(
(%SYSFUNC(abs(%sysfunc(scan(&entire,3,'/'))),4.)),
(%sysfunc(abs(%sysfunc(scan(&entire,1,'/'))),z2.),$2.)
),$6.);
%sysfunc(quote(&com))
%end;%end;


%else %if &I EQ %sysfunc(YEAR("&sysdate"d)) %then %do;
%do months=01 %to %sysfunc(month("&sysdate"d));
%let entire= %sysfunc(abs(%sysfunc(mdy(&months,01,&i))),mmddyy10.);
%let com=%sysfunc(cats(
(%SYSFUNC(abs(%sysfunc(scan(&entire,3,'/'))),4.)),
(%sysfunc(abs(%sysfunc(scan(&entire,1,'/'))),z2.),$2.)
),$6.);
%sysfunc(quote(&com))
%end;%end;

%end;
%mend;


It is useful and works fine, see examples below, but does it exist some
other macro over there or among SAS-L users that makes the same functionality?
Could you do some tweaks or improve my macro code for a shorter handcode and
more robust execution?

%* we check the list of elements since (initial yr,intitial month);
%put %yrmonth(1997,12);

data test;
input a $  ;
cards;
199908
200003
200305
200306
200502
200903
200911
200801
200712
;;run;


data test2;
set test;
IF  A in (%yrmonth(2005,01));
run;

data test3;
set test (WHERE=( A in (%yrmonth(1999,01)) )) ;
run;

PROC SQL;
create table test4 as
select * from test
where A in (%yrmonth(2003,06));
quit;


Daniel Fernández.
Barcelona.