topic Re: Delete rows based on date in SAS Programming

Delete rows based on date

tv80 — Tue, 29 May 2018 10:44:03 GMT

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?

Re: Delete rows based on date

RW9 — Tue, 29 May 2018 10:56:00 GMT

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;

Re: Delete rows based on date

tv80 — Tue, 29 May 2018 11:43:13 GMT

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

Re: Delete rows based on date

ballardw — Tue, 29 May 2018 22:36:48 GMT

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