how to use INTNX/INTCK function effectively?

[MarkWik]

Hey folks,

I'd appreciate if any of you could explain to how to use the INTNX/INTCK function effectively, is it more effective in Proc sql or datastep. To get Observations between months, between dates between years or ever between some timestamp with hours,date, min, sec. Can either of you explain me with a clear example. I have of course read the support.sas.com and some university publications, however for my level of understanding and grasping i seek a finer explanation that is comprehensive . If you have got the time to spare a little for me, I'd most appreciate it. Thanks very much indeed.

Also, folks who are new to sas like me. Wake up to these 2 functions, a definite interview question if you are a potential job applicant - a sas programmer/analyst.

[kuridisanjeev]

Hi You can go through this file.This will explain a lot about dates and date functions in SAS.

Thanks & Regards..

Sanjeev.K

[Patrick]

Sanjeev posted a quite neat paper. Only thing missing is an update to include SAS 9.2 and 9.3 enhancements.

"is it more effective in Proc sql or datastep"

The most important thing here is that when used in code accessing table in a database some SAS date functions won't be translated into database specific functions and therefore only a partial SQL can be sent to the database for execution. This can lead to severe performance degredation if the not "sendeble" part would significantly reduce datavolumes transfered from the database back to SAS for further execution.

Which date functions can be translated into database specific functions is SAS version and database specific and documented in the SAS Access\<your database> docs.

[cwilson]

I use INTNX to track product sales by week, for example, where it is very efficient to code sales date into a week number.  I would not use INTNX to calculate someone's age, because the function is using calendar dates, like Jan 1st, etc. for the interval boundaries.  Also, it is worth doing testing to understand INTNX especially for "week", because Jan. 2, for example, may not give you the value you thought, depending on which year it is, and what day of the week Jan. 1st falls on.

As for PROC SQL - I love PROC SQL, but that doesn't mean it's the right tool for everything you do.  ("When all you have is a hammer, everything looks like a nail.")  I prefer a DATA step for SAS functions, like the date functions.