LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.

Back to: Top of message | Previous page | Main SAS-L page