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 14:10:04 -0700
Reply-To:     jfh@stanfordalumni.org
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: An utility macro about dates.
Comments: To: Daniel FernŠndez <fdezdan@GMAIL.COM>
In-Reply-To:  <20e5d12f0910070306i7242710crfa25ba87d6d372e5@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"

It's often said on SAS-L that you should use SAS date values when working with dates, and this is another case where that is true.

Remove the leading ">", which is there to preserve indentation

===== 1 %macro yrmonth2(start_year=2010, start_month=1); 2 3 %local iter_month_dt end_month_dt; 4 5 %let iter_month_dt = %sysfunc(mdy(&START_MONTH., 1, &START_YEAR.)); 6 %let end_month_dt = %sysfunc(intnx(month, "&SYSDATE9."d, 1)); 7 8 %do %while (&ITER_MONTH_DT. < &END_MONTH_DT.); 9 "%sysfunc(putn(&ITER_MONTH_DT., yymmn6.))" 10 %let ITER_MONTH_DT = %sysfunc(intnx(month, &ITER_MONTH_DT., 1)); 11 %end; 12 13 %mend yrmonth2; 14 15 %put %yrmonth2(start_year=2008, start_month=5); =====

prints:

===== "200805" "200806" "200807" "200808" "200809" "200810" "200811" "200812" "200901" "200902" "200903" "200904" "200905" "200906" "200907" "200908" "200909" "200910" =====

The revised code is two-thirds shorter, and arguably easier to understand.

To make this a production macro, you should check the parameters to make sure that a valid year and month were specified. You might want to consider making the end year and month into parameters, defaulting to this month, in case you want to run the program for a past or future month. You might want to use SAS date values for the parameters instead of strings. And finally, you might want to make the quotes optional.

On Wed, 7 Oct 2009 12:06:38 +0200, "Daniel FernŠndez" <fdezdan@GMAIL.COM> said: > 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.

-- Jack Hamilton Sacramento, California jfh@alumni.stanford.org <== Use this, not jfh @ stanfordalumni.org

Tots units fem forÁa!


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