BookmarkSubscribeRSS Feed
katiauk
Calcite | Level 5

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

katiauk
Calcite | Level 5

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
katiauk
Calcite | Level 5

@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.

Reeza
Super User
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)

katiauk
Calcite | Level 5

@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
Reeza
Super User

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
Super User
The key is use of the TODAY and INTNX functions. Today provides the date and intnx allows you to increment it by choice.
katiauk
Calcite | Level 5

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1907 views
  • 1 like
  • 3 in conversation