Desktop productivity for business analysts and programmers

DATEPART: what code to use to automatically change month part

Reply
Occasional Contributor
Posts: 8

DATEPART: what code to use to automatically change month part

[ Edited ]

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

Super User
Super User
Posts: 7,720

Re: DATEPART: what to use code to read the dates are between current month without manually changing

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. 

Occasional Contributor
Posts: 8

Re: DATEPART: what to use code to read the dates are between current month without manually changing


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.

Super User
Super User
Posts: 7,720

Re: DATEPART: what to use code to read the dates are between current month without manually changing

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;
Occasional Contributor
Posts: 8

Re: DATEPART: what to use code to read the dates are between current month without manually changing


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.

Super User
Posts: 19,165

Re: DATEPART: what to use code to read the dates are between current month without manually changing

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)

Occasional Contributor
Posts: 8

Re: DATEPART: what to use code to read the dates are between current month without manually changing


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
Super User
Posts: 19,165

Re: DATEPART: what to use code to read the dates are between current month without manually changing

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;
Super User
Posts: 19,165

Re: DATEPART: what code to use to automatically change month part

The key is use of the TODAY and INTNX functions. Today provides the date and intnx allows you to increment it by choice.
Occasional Contributor
Posts: 8

Re: DATEPART: what code to use to automatically change month part


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! Smiley Wink

 

Thanks.

Super User
Super User
Posts: 7,720

Re: DATEPART: what code to use to automatically change month part

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.

Ask a Question
Discussion stats
  • 10 replies
  • 569 views
  • 1 like
  • 3 in conversation