DATA Step, Macro, Functions and more

Delete rows based on date

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Delete rows based on date

Hi,

 

I have a table where I need to delete rows based on two fields, one of which is a date field in YYMMDDN8. format. The idea is to delete rows where the date is not 20170102. I tried a simple PROC SQL command to delete these rows (the date field is marked as 'y' here):

 

proc sql;

delete

from work.table

where x='12345678' and y ne 20170102;

 quit;

 

What happens is the command deletes all of the rows where x='12345678' because the date field is not in the right format. How do I need to change the code so that it works?

 


Accepted Solutions
Solution
4 weeks ago
Super User
Super User
Posts: 9,441

Re: Delete rows based on date

Use a date literal, or input text to a SAS date numeric.

proc sql;
  delete from work.table 
  where x='12345678' and y ne "02JAN2017"d;
quit;

Note the quotes and the d, this way only accepts ddmmmyyyy format.

The other:

proc sql;
  delete from work.table 
  where x='12345678' and y ne input('20170102',yymmdd8.);
quit;

View solution in original post


All Replies
Solution
4 weeks ago
Super User
Super User
Posts: 9,441

Re: Delete rows based on date

Use a date literal, or input text to a SAS date numeric.

proc sql;
  delete from work.table 
  where x='12345678' and y ne "02JAN2017"d;
quit;

Note the quotes and the d, this way only accepts ddmmmyyyy format.

The other:

proc sql;
  delete from work.table 
  where x='12345678' and y ne input('20170102',yymmdd8.);
quit;
New Contributor
Posts: 2

Re: Delete rows based on date

Thank you, that worked perfectly! I had tried something similar to the first option, but with single quotation marks.

Super User
Posts: 13,358

Re: Delete rows based on date


@tv80 wrote:

Thank you, that worked perfectly! I had tried something similar to the first option, but with single quotation marks.


Single quotes should only have an impact if you are also using macro variables OR if you had spaces between any of the digits and the month name.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 109 views
  • 0 likes
  • 3 in conversation