Delete rows based on date

[tv80]

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?

[RW9 · Accepted solution]

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;

[tv80]

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

[ballardw]

---

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