I see three problems with this particular code:
1) intx should be intnx (trivial, and you said it was untested).
2) I don't know what kind of optimization SAS would does for the
functions in the where clause, but I suspect that it can't do any (and
absent documentation to the contrary we have to assume that it doesn't).
That means that the intnx and date functions will be called twice each
for each record. It would be better to save the from-date and to-date
values as constants (or generate them using macro functions) and use
3) The date function returns the current date, so if a job runs over a
month boundary different criteria will be used for different records. I
suppose if you're really unlucky the two months might even be different
in the same evaluation of the where clause. I suspect that that's not
what is wanted.
This is my suggestion for the where clause:
where date between %sysfunc(intnx(month,"&SYSDATE9."d, -13))
and %sysfunc(intnx(month,"&SYSDATE9."d, -12))
If you have turned on the appropriate tracing and debugging options,
you might want to use this code so the dates will appear in the log in
where date between "%sysfunc(intnx(month,"&SYSDATE9."d, -13),
and "%sysfunc(intnx(month,"&SYSDATE9."d, -12), date9.)"d
Note that I used &SYSDATE9 instead of the date() function, so the
results will be different from the original poster's proposed code when
there's a month boundary between the start of the job and when the where
statement is executed. That could be mitigated, at the expense of more
complicated code, by using sysfunc() and date() inside the outer
sysfunc, or by setting a user macro variable (probably easier). So that
%let boundary = %sysfunc(date());
where date between "%sysfunc(intnx(month,&BOUNDARY., -13),
and "%sysfunc(intnx(month,&BoUNDARY., -12), date9.)"d
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Coelum, non animum mutant, qui trans mare currunt.
>>> "toby dunn" <tobydunn@HOTMAIL.COM> 01/07/2005 6:08 AM >>>
Proc SQL would be a very easy way to do this with a where statement and
between, I guess it would depend on your data structure to how
this would be, since SQL like a standardized data structure the best
look at a example:
lets say we have a data set with a variable called 'date' that houses
data you want to compare too.
create table new_data as
where date between intx('month',date(),-13) and
of course this assumes that your date value in the table policy_table
sas date value.
From: Chad Webb <purplepride@GMAIL.COM>
Reply-To: Chad Webb <firstname.lastname@example.org>
Subject: Date Comparisons with Proc SQL.
Date: Fri, 7 Jan 2005 07:24:23 -0600
I need a quick way to compare a date range in proc sql. Any help would
Run a process monthly and need to compare a value created on new
monthly with value that was created between 12 - 13 months ago.
run_date = today();
select * from connection to <>
create table a as
select policy, value
where run_date - 13 months <= effct_date <= run_date - 12
"MMS <firsthealth.com>" made the following annotations.
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.