Rolling dates Proc SQL using DATE()

[pcfriendly]

 
Hi

I have a table with a column containing dates in the future.

I want to split these up into records from todays date to the date in 3 months time, 3 - 6 months time etc etc.

I want to have this work the same way each time I run it so I've been trying to write this using DATE() for todays date as a rolling date whenever I run the code.

And then 3 months time as (DATE() + 90).

Here's my code;

---------------------------------------------------------------------------

PROC SQL;
   CREATE TABLE WORK.DATE_TIME_TEST_ONE AS
   SELECT *
      FROM CDR_LIVE.INACTIVE_PPAID_SVCS_BSLN t1

      WHERE t1.CED BETWEEN DATE()

      AND (DATE() + 30);

QUIT;

----------------------------------------------------------------------------

Can anyone help me with how to get this to work?

Thanks

[Haikuo]

Hi,

You are mixing concept of 'month' and 'days' here. Maybe it doesn't matter for your project, but if it is, a month would be from 28 days to 31 days. Your code will work if your measure is based on days, otherwise, please check intnx() for details.

Haikuo

[pcfriendly]

Thanks Haikuo

I know what you mean about the months & days thing.

For now though I will call it what it is & name it something like 'Now to 90 days from now'.

In the tradition of some error fixing itself just as you ask some guru for help, It actually worked this morning...

Could'nt get it work yesterday no matter what I did .... as soon as I sent this question, to the forum , No problem ...

Can you tell me more about how intnx() works & how I mark a question as answered on the forum? Thanks again 

[Haikuo · Accepted solution]

Sure, I can show you an example based on your code and explain it in a nutshell, but you better off to look for details using SAS online help doc, as it is a powerful function of processing 'date'.

PROC SQL;

   CREATE TABLE WORK.DATE_TIME_TEST_ONE AS

   SELECT *

      FROM CDR_LIVE.INACTIVE_PPAID_SVCS_BSLN t1

      WHERE t1.CED BETWEEN DATE()

      AND intnx('month',DATE(),1,'sameday');

QUIT;

Here 'month' means the shifting unit, it could be 'week', 'year', 'quarter' and many many others. If you are to count days straight, your way works; while if you are to count week days only( excluding Sat, Sun), this is the way to go, just use 'weekday' as the unit. '1' after date() is the amount you want to shift, here is 1 month. it could be 0 or negative which is used to roll back dates. 'sameday'  is to called 'alignment', it is to tell SAS how to process the unit month, 'sameday' means the same day of the month here, so today is 7/19, then you will get 8/19. As you can guess, the 'alignment' could be as well 'b' or 'e', meaning the beginning or end of the shifting unit.

If you can see the link under any of answers to your question "correct answer", "helpful answer" etc, click it, that will mark question as 'answered'.

HTH

Haikuo

[pcfriendly]

Interesting, I'll have a look at that, Thanks