Hi:
This is not an ODS question.
In general, when working with SAS Macro variables, it is a good idea to have a working SAS program BEFORE you convert the program to use SAS macro variables.
For example, given this data file:
[pre]
data table1;
infile datalines;
input datecolumn : mmddyy10. column1;
return;
datalines;
03/01/2007 1111
04/15/2007 2222
05/16/2007 3333
06/18/2007 4444
07/04/2007 5555
;
run;
ods listing;
proc print data=table1;
title 'note how dates are internally stored as numbers';
run;
[/pre]
No matter HOW the date value is read into SAS, note how the datecolumn variable is represented in PROC PRINT without any formatting -- date values are just the number of days from Jan 1, 1960 (the zero date in SAS):
[pre]
note how dates are internally stored as numbers
Obs datecolumn column1
1 17226 1111
2 17271 2222
3 17302 3333
4 17335 4444
5 17351 5555
[/pre]
So, today's date is 17335 and 60 days before today is 17275 (17335 - 60). If I had used a format statement in the PROC PRINT, the date variable would STILL be a number internally, SAS just would have displayed that number in a readable date format.
Once you know that the date is stored as a number internally, then you can build the correct SQL WHERE clause to do the selection. Consider these 4 SQL queries -- each one is attempting to run against TABLE1 -- using a different form for the date value. Only 2 of these queries will work:
[pre]
** this query will work;
title '1: Use a number that represents the date';
proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between 17275 and 17335;
quit;
** this query will work;
title '2: Use a date constant in the WHERE clause';
proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between "19Apr2007"d and "18jun2007"d;
quit;
** this query will not work because it is not the right;
** form for a SAS date constant;
title '3: Try to use "mm/dd/yyyy"d as a date constant in the WHERE clause';
proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between "04/19/2007"d and "06/18/2007"d;
quit;
** this query will not work because the internal value;
** for datecolumn is a NUMBER and the dates as shown below;
** are invalid in the query;
title '4: Try to use mmddyy10 form in the WHERE clause';
proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between 04/19/2007 and 06/18/2007;
quit;
[/pre]
When you use macro variables, you are creating TEXT strings. So, when you are manipulating the date variables as macro variables, what you are doing here:
%let beg_date = &end_date – 60;
is the equivalent of trying to do this: "06/18/2007" - 60
Even if 06 divided by 18 divided by 2007 would evaluate in the %LET statement, it would NOT be a date value anymore.
For more help with this code and macro variable reference issues, your best bet is to contact Tech Support.
cynthia