DATA Step, Macro, Functions and more

Macro For A Date Range

Reply
N/A
Posts: 0

Macro For A Date Range

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?
Valued Guide
Posts: 2,177

Re: Macro For A Date Range

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Macro For A Date Range

good done
thank you
SAS Employee
Posts: 58

Re: Macro For A Date Range

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 3 replies
  • 237 views
  • 0 likes
  • 3 in conversation