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
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
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
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
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
Interesting, I'll have a look at that, Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: