topic Re: SAS DATE MANIPULATION in SAS Procedures

SAS DATE MANIPULATION

Benn — Mon, 01 Oct 2012 19:48:51 GMT

Hi Guys,

I am very new to SAS. Hope to get some advice for the following.

I currently have decades of data. And they have dates in the format of YYYYMMDD. On every date there are customers (eg. A, B, C, D, E).

What I am trying to do is to extract the observations for each customer (A,B,C,D,E) for the last 2 days of each month.

I am not sure how to get about doing this in an efficient manner.

Thanks guys!

Re: SAS DATE MANIPULATION

Haikuo — Mon, 01 Oct 2012 20:15:56 GMT

If you only care about calendar date, here is an option:

data want;

set have;

if Your_date = intnx('month',Your_date,0,'end') or

Your_date=intnx('month',Your_date,0,'end')-1;

run;

Haikuo

Re: SAS DATE MANIPULATION

RichardinOz — Tue, 02 Oct 2012 03:44:27 GMT

If it is a large dataset you might want to use a where statement with a single inequality condition for efficiency, as in:

data want ;

set have ;

where Your_date > intnx('month',Your_date,0,'end') - 2 ;

run ;

The where statement can also be coded as a (where= ()) dataset option

set have

(where = (Your_date > intnx('month',Your_date,0,'end') - 2))

;

Re: SAS DATE MANIPULATION

MikeZdeb — Tue, 02 Oct 2012 13:19:47 GMT

hi ... not much difference in performance between IF and WHERE any more ...

"Performance in these examples is close enough that the choice of an IF statement versus a WHERE statement versus a WHERE option is arbitrary."

from ...

Efficiency Considerations Using the SAS System

Rick Langston, SAS Institute

http://www2.sas.com/proceedings/sugi30/002-30.pdf

Re: SAS DATE MANIPULATION

Haikuo — Tue, 02 Oct 2012 13:38:55 GMT

Not necessarily. Art and I had done some tests before, and for many cases, 'if' is faster than 'where', and the benefit becomes more evident when hit rate is higher and the table is larger. I believe it is due to data step enhencement on sequential processing. Random access such as 'where' may bear an edge when hit rate is less than 2-4%, and of course, when there is an index.

Haikuo

Re: SAS DATE MANIPULATION

RichardinOz — Tue, 02 Oct 2012 14:04:37 GMT

Point taken, Mike.

But Rick goes on to say

"However, if the subsetting is to be performed using a LIBNAME engine against

a database that is optimized for WHERE processing, then the WHERE choice is preferred. "

This is usually the case for the data I work with. I would only use a subsetting IF when the data step has to perform intermediate calculations before entire rows can be accepted or rejected.

My other objection is aesthetic. The WHERE syntax conforms to SQL standards and is in my view more readable. An IF statement without an explicit THEN can be confusing. I would rather reverse the condition to make it more explicit:

IF (reject condition is met) THEN DELETE ;