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 those.

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 human-readable form:

===== where date between "%sysfunc(intnx(month,"&SYSDATE9."d, -13), date9.)"d 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 would be:

===== %let boundary = %sysfunc(date()); where date between "%sysfunc(intnx(month,&BOUNDARY., -13), date9.)"d and "%sysfunc(intnx(month,&BoUNDARY., -12), date9.)"d =====

-- JackHamilton@FirstHealth.com 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 >>> Chad,

Proc SQL would be a very easy way to do this with a where statement and the between, I guess it would depend on your data structure to how difficult this would be, since SQL like a standardized data structure the best lets look at a example:

lets say we have a data set with a variable called 'date' that houses the data you want to compare too.

<untested>

proc sql; create table new_data as select * from policy_table where date between intx('month',date(),-13) and intx('month',date(),-12); quit;

of course this assumes that your date value in the table policy_table is a sas date value.

HTH Toby Dunn

From: Chad Webb <purplepride@GMAIL.COM> Reply-To: Chad Webb <purplepride@gmail.com> To: SAS-L@LISTSERV.UGA.EDU 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 be great.

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();

proc sql; select * from connection to <> create table a as select policy, value from policy_table where run_date - 13 months <= effct_date <= run_date - 12 months; quit;

Thanks,

Chad

"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. Thank you. =============================================================================