Hello,
I have a question I couldn't even search for an answer because I have no idea what to seach for! Here's the deal: I wrote this very simple PROC SQL:
PROC SQL;
DELETE FROM TASK_Current1
WHERE DATEPART(TASK_Current1.LAST_UPDATE_DATE) NOT BETWEEN '21NOV15'D AND '20DEC15'D
;
QUIT;
It works just fine, but I wanted to include it in a scheduled process, so I wouldnt have to go into the code every month and change the dates. Is there a way to code it to understad that the month part is previous and current month, so for example:
In Jan I'd change the dates to '21DEC15'D AND '20JAN16'D,
In Feb I'd change the dates to '21JAN16'D AND '20FEB16'D, and so on....
Thanks!
Katia
If its always 21 of last month to 20 of this month, then why not do it programmatically rather than entering it:
proc sql; delete from TASK_CURRENT1 where datepart(TASK_CURRENT1.LAST_UPDATE_DATE) not between mdy(month(today()-1,21,15) and mdy(month(today(),20,15); quit;
(Note, use consistent casing and indentations in code helps readability). The above assumes all the data is in one year of course, you will need to jig it a bit if you have multiple years. What I would say though is you may be better of using datastep for this, might make coding a bit easier if you have multiple years.
@RW9 wrote:If its always 21 of last month to 20 of this month, then why not do it programmatically rather than entering it:
proc sql; delete from TASK_CURRENT1 where datepart(TASK_CURRENT1.LAST_UPDATE_DATE) not between mdy(month(today()-1,21,15) and mdy(month(today(),20,15); quit;(Note, use consistent casing and indentations in code helps readability). The above assumes all the data is in one year of course, you will need to jig it a bit if you have multiple years. What I would say though is you may be better of using datastep for this, might make coding a bit easier if you have multiple years.
RW9,
That's exactly what I want to do. And yes, the dates are always 21 of last month to 20 of this month. It will only be multiple years in January, where it has to delete dates that are not between 21DEC15 and 20JAN16.
I have tried what you suggested, but it errors:
34 35 PROC SQL; 36 DELETE FROM TASK_TEMPO_Current1 37 WHERE DATEPART(TASK_TEMPO_Current1.LAST_UPDATE_DATE) NOT BETWEEN mdy(month(today()-1,21,15) and mdy(month(today(),20,15) 38 ; _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. 2 The SAS System 08:34 Monday, January 11, 2016 ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 39 40 QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.10 seconds cpu time 0.00 seconds
Thanks.
Yes, typo there as I didn't have any data to work with i can't test it:
proc sql; delete from TASK_CURRENT1 where datepart(TASK_CURRENT1.LAST_UPDATE_DATE) not between mdy(month(today())-1,21,15) and mdy(month(today()),20,15); quit;
@RW9 wrote:Yes, typo there as I didn't have any data to work with i can't test it:
proc sql; delete from TASK_CURRENT1 where datepart(TASK_CURRENT1.LAST_UPDATE_DATE) not between mdy(month(today())-1,21,15) and mdy(month(today()),20,15); quit;
I have tried the above and got the following error:
35 PROC SQL; 36 DELETE FROM TASK_Current1 37 WHERE DATEPART(TASK_Current1.LAST_UPDATE_DATE) NOT BETWEEN mdy(month(today())-1,21,15) and 37 ! mdy(month(today()),20,15); NOTE: Invalid argument to function MDY. Missing values may be generated. NOTE: 2472 rows were deleted from TASK_CURRENT1.
The data I am working with looks more or less like this:
ID LAST_UPDATE_DATE TYPE STATUS TITLE
36222000 18DEC15:16:04:10 FinalReview CLOSED Send for Approval
31109000 30DEC15:17:00:38 FinalReview OPEN Send for Approval
So, the code should remove the first line from the table, because the date is not on or after the 21st of the previous month and on or before the 20th of the current month.
mdy(month(today())-1,21,15)
This line will cause issues, because if the month is 1, then it will return 0, which is not a valid month.
Use INTNX on the date instead.
UNTESTED:
intnx('month', mdy(month(today), 21, 15) , -1)
@Reeza wrote:mdy(month(today())-1,21,15)
This line will cause issues, because if the month is 1, then it will return 0, which is not a valid month.
Use INTNX on the date instead.
UNTESTED:
intnx('month', mdy(month(today), 21, 15) , -1)
I have tried a select query to test it (I added () after today, because it did not recognize today on its own):
PROC SQL;
SELECT * FROM TASK_Current1
WHERE DATEPART(TASK_Current1.LAST_UPDATE_DATE) NOT BETWEEN
intnx('month', mdy(month(today()),21,15),-1) and intnx('month',mdy(month(today()),20,16),1)
;
QUIT;
I've got no error (log below), but it did not exclude the all the rows - it should have returned just one line and it returned the whole table.
14 PROC SQL; 15 SELECT * FROM TASK_Current1 16 WHERE DATEPART(TASK_Current1.LAST_UPDATE_DATE) NOT BETWEEN 17 intnx('month', mdy(month(today()),21,15),-1) and intnx('month',mdy(month(today()),20,16),1) 18 ; NOTE: No rows were selected. 19 20 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.34 seconds cpu time 0.01 seconds
Ok...back to the starting point.
First, I create a table to test my start/end intervals:
data have;
do i=1 to 12;
test_date=mdy(i, 4, 2015);
start= intnx('month', test_date, -1, 'b')+20;
end= intnx('month', test_date, 0, 'b')+19;
output;
end;
format test_date start end date9.;
run;
proc print;
run;
Once you're sure the formula's are correct you can change test_date to today() and place in your WHERE clause.
PROC SQL;
SELECT * FROM TASK_Current1
WHERE DATEPART(TASK_Current1.LAST_UPDATE_DATE) NOT BETWEEN
(intnx('month', today(), -1, 'b')+20) and (intnx('month', today(), 0, 'b')+19);
;
QUIT;
@Reeza wrote:
The key is use of the TODAY and INTNX functions. Today provides the date and intnx allows you to increment it by choice.
Reeza,
Can I use those functions with proc sql (I know the use of TODAY, but INTNX I have never used)? I do apologise if it seems obvious, but my excuse is that I am very new to SAS!
Thanks.
Yes, you can use most SAS functions in SQL as long as you are using the SAS SQL engine, i.e. pass-through is different - if you don't know what that is just ignore it.
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 25. 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.