topic Macro For A Date Range in SAS Programming

Macro For A Date Range

deleted_user — Wed, 11 Aug 2010 13:53:57 GMT

I currently have the following:

proc sql;
select
a.id,
b.logon_id,
b.enter_date,
b.cd,
b.bef_desc,
b.aft_desc
from
fs.ROOT_VIEW a,
fs.t_trans b
where
a.id=b.id and
enter_date between to_date(07/01/2010,mm/dd/yyyy') and to_date(07/01/2010,mm/dd/yyyy') ;

I want to write a macro so that when this runs monthly, I won't have to physically change the date range. I'm not sure how to go about this since it's a Proc SQL. Any suggesstions?

Re: Macro For A Date Range

Peter_C — Wed, 11 Aug 2010 15:17:03 GMT

I don't think "to_date(07/01/2010,mm/dd/yyyy') " is base SAS syntax. You might use something like that in "explicit pass-through sql" subject to the sql flavor of the underlying dbms.
SAS becomes simpler if you can use "implicit pass-through sql queries". Then your date filter clause can be just [pre] and enter_date between "&from_date"d and "&to_date"d[/pre] where macro variables &from_date and &to_date are filled in base SAS code to look like 10Aug2010 and 21Jul2010.
The handling of implicit-pass-through syntax by PROC SQL will re-present SAS date constants in the query that is passed to the dbms so that they are in the style that that dbms needs.

sorry it is sounding convoluted - but that is the way it seems (to me).

peterC

Re: Macro For A Date Range

deleted_user — Mon, 30 Aug 2010 16:35:03 GMT

good done
thank you

Re: Macro For A Date Range

PatrickG — Mon, 13 Dec 2010 15:54:11 GMT

Lots of good info on using SAS dates here. Hopefully, this will help.

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a002200738.htm