Since macro can generate anything from nothing to multiple data and proc steps, it is not trivial to design an "elegant" macro. I prefer writing short utility macros, but it is just me. Hope this helps a bit. %*-- returns "between (first day of month) and (last day of month)" --*; %macro isInMonth(ofDate=%sysfunc(today())); %local first last; %let first = %sysfunc(intnx(mon,&ofDate,0,b)); %let last = %sysfunc(intnx(mon,&ofDate,0,e)); %*;between &first and &last %mend isInMonth; %*-- test data --*; options mprint; data one; do date = "01jan2012"d to "31aug2012"d; output; end; format date date10.; run; %*-- usage example -- I prefer this --*; %let today = %sysfunc(today()); %*-- same day last month --*; %let sdlm1 = %sysfunc(intnx(mon, &today, -1, s)); %let sdlm2 = %sysfunc(intnx(mon, &today, -2, s)); data d1 d2; set one; where date %isInMonth(ofDate=&sdlm1) or date %isInMonth(ofDate=&sdlm2); select(month(date)); when(month(&sdlm1)) output d1; when(month(&sdlm2)) output d2; otherwise; end; run; %*-- if you insist on two sql procs wrapped up in a macro... --*; %macro extract(select, outPrefix=ex, date=%sysfunc(today())); %local sdlm1 sdlm2 i; %do i = 1 %to 2; %let sdlm&i = %sysfunc(intnx(mon, &date, -&i, s)); proc sql; create table &outPrefix.&i as %unquote(&select) where date %isInMonth(ofDate=&&sdlm&i); quit; %end; %mend extract; %*-- usage example --*; %extract(%nrstr( select * from one )) %*-- check --*; proc compare base=d1 compare=ex1; run; proc compare base=d2 compare=ex2; run; %*-- on lst, in part NOTE: No unequal values were found. All values compared are exactly equal. NOTE: No unequal values were found. All values compared are exactly equal. --*;
... View more