Running same report with different dates

[deleted_user]

I am trying to figure out how to run a query based on term dates and effective dates for members. I have to qualify by term date > first day of that month and effective date less than the last day of the month. The results will be for that month. I was wondering if there was another way I could run this without doing 12 process flow for the whole year. Also I need to append the data into 1 table. Any hints on how to accomplish this.

[Cynthia_sas]

Hi:
This previous post may be useful to you.
http://support.sas.com/forums/thread.jspa?messageID=6674ᨒ
It uses SAS Macro variables. So you could build a "generic" WHERE clause from the current date, something like this:
[pre]

%let thisdate = %sysfunc(today());
%let thisbeg = %sysfunc(intnx(month,&thisdate,0,beginning));
%let thisend = %sysfunc(intnx(month,&thisdate,0,end));
%let fmtbeg = %sysfunc(putn(&thisbeg,date9.));
%let fmtend = %sysfunc(putn(&thisend,date9.));
%let wherecls = where mydatevar between &thisbeg and &thisend;
%let where2 = where mydatevar between "&fmtbeg"d and "&fmtend"d;

%put ***************************;

%put thisdate= &thisdate;
%put thisbeg= &thisbeg;
%put thisend= &thisend;
%put fmtbeg = &fmtbeg;
%put fmtend = &fmtend;
%put where with dates as numbers: &wherecls;
%put alternate where with dates as char string: &where2;
%put ***************************;
[/pre]

Which generates this output in the LOG and the %PUTs are just showing you how the values of the macro variables are being set. (White space was added here for ease of reading).
[pre]
243 %put ***************************;
***************************

244
245 %put thisdate= &thisdate;
thisdate= 17442

246 %put thisbeg= &thisbeg;
thisbeg= 17440

247 %put thisend= &thisend;
thisend= 17470

248 %put fmtbeg = &fmtbeg;
fmtbeg = 01OCT2007

249 %put fmtend = &fmtend;
fmtend = 31OCT2007

250 %put where with dates as numbers: &wherecls;
where with dates as numbers: where mydatevar between 17440 and 17470

251 %put alternate where with dates as char string: &where2;
alternate where with dates as char string: where mydatevar between "01OCT2007"d and "31OCT2007"d

252 %put ***************************;
***************************

[/pre]
and those 2 where clauses are:
[pre]
where mydatevar between 17440 and 17470
OR
where mydatevar between "01OCT2007"d and "31OCT2007"d
[/pre]

So, if MYDATEVAR is a SAS date value, then the first WHERE clause is comparing the date to the internal "number" for Oct 1 and Oct 31, where the second WHERE clause is using a Date Constant so you can have a readable date in the WHERE clause.

You may need to contact Tech Support for help figuring out how to modify the code in your project to use the macro variables in the right place. And for help with %SYSFUNC or the INTNX function.

cynthia