Date:         Thu, 8 Jul 2010 11:57:47 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: The expensive INTNX function  (was: Pass date values)
Comments: To: "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
In-Reply-To:  <E0B423A8C0D1E74B8905B2C5CB38C1AF067A17@GENO3.wharton.upenn.edu>
Content-Type: text/plain; charset="us-ascii"

Mark:
Interesting finding ....

Have you tried defining the variable and including the predefined variable in your WHERE clause? In other words, will the CALCULATED value (a logical value) of the comparison in a SQL query (or a previously assigned value in a Data step) increase processing time as much? Clearly a SQL compiler will have more of a problem optimizing a search based on a function that likely would be evaluated at run time. In this instance the comparison to what amounts to a constant shouldn't take much time to compute in a SELECT clause. A DROP= option could remove the logical value.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Keintz, H. Mark
Sent: Thursday, July 08, 2010 10:08 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: The expensive INTNX function (was: Pass date values)

Mark et. al.:

Whenever selecting a subset based on dates belonging to a particular
year and month, I suggest avoiding using the seductive, but
expensive INTNX function.  In other words this clause:

   where intnx('month',xyz_date,0,'b') eq &theMonthDate

is much more resource intensive than this:

  where date between &theMonthDATE and &MonthendDATE


Here I have defined macrovar MonthendDATE as

   %let MonthendDATE=%sysfunc(intnx(month,&theMonthDATE,0,E));


I did a test of these two approaches, selecting 1,860,000
qualifying records from a dataset of 43,860,000 observations.
The "where between" took 3 seconds, while the "where intnx"
took 28 seconds.

I suppose this is a property of all functions that monotonically
collapse values, (i.e. "where floor(x)=10" vs. "where 10<=x<11").
But I suspect it's just not as apparent with date intervals as
with other functions.

In other words, use INTNX only for special occasions if possible.


Regards,
Mark


> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Terjeson, Mark
> Sent: Wednesday, July 07, 2010 12:32 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Pass date values
>
> Hi Mike,
>
> Here is one approach:
>
>
> The first datastep creates a macro variable
> that contains a SAS date built from the user
> entered info. The day is forced to day 1.
> Then the SQL can compare two SAS dates and
> we use the INTNX() function to fold the day
> to 1 with the 'beginning' flag. This way any
> row in the data table from Jan 1 thru 31, 1995
> will match successfully.  Of course, if you
> **know** your data, and if it is already only
> has day values of 1 then you can skip the
> INTNX() function which is there to capture the
> rows with day 2 through 31 along with day 1.
>
>
>
> %let month=january;
> %let year=1995;
>
> proc format;
>     invalue MonNum
>     'january' = 1
>     'febuary' = 2
>     'march' = 3
>     'april' = 4
>     'may' = 5
>     'june' = 6
>     'july' = 7
>     'august' = 8
>     'september' = 9
>     'october' = 10
>     'november' = 11
>     'december' = 12
>     ;
> run;
>
>     * convert user info to a sas date ;
> data _null_;
>     EnteredDate =
>         mdy(input(lowcase("&month"),MonNum.),
>         1,&year);
>     call symput('theMonthDate',EnteredDate);
> run;
>
> %put _global_;
>
>     * select dates with the same month ;
> proc sql;
>     select * from xyz
>     where intnx('month',xyz_date,0,'b')
>         eq &theMonthDate;
> quit;
>
>
>
> Hope this is helpful.
>
>
> Mark Terjeson
> Investment Business Intelligence
> Investment Management & Research
> Russell Investments
> 253-439-2367
>
>
> Russell
> Global Leaders in Multi-Manager Investing
>
>
>
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> SUBSCRIBE SAS-L Joe H. Smith
> Sent: Tuesday, July 06, 2010 11:29 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Pass date values
>
> dear all;
>
> i have data as date values in date9. format,i will enter values from
> external browser
> as month=january and year=1995,how shld i write code to compare this
> values with the values in data set,
>
> proc sql;
> select * from xyz
> where mn=&month. and yr=&year.
> quit;
>
>
> Thanks
> Mike