DATA Step, Macro, Functions and more

Deleteteing data in SAS between 2 dates

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Deleteteing data in SAS between 2 dates

Hi, I have about 18 million rows of data and a lot of the data is pretty old and it goes back to 2009. One of the columns is a date field called fdate and the format is 01/01/2017 it is also in text, I would like to delete all records that have a date between 01/01/2009 and 31/12/2010. What is the best way to do this? I have included below an example of the data in question, the data included below is only test data. Your help in this matter would be much appreciated

 

I am using SAS EG 7.1

 

mprReadDateSourcereasonrej1rej2rej3rej4fnamefdate
10020101210ENaaabbb  ABC13/12/2010

Accepted Solutions
Solution
‎05-09-2017 09:08 AM
Super User
Posts: 7,854

Re: Deleteteing data in SAS between 2 dates

& is a macro trigger, it has to be followed by a macro variable name, not by a value or literal. If you want to use macro variables, use my code AS IS.

If not, use the date literals without any additional characters.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,988

Re: Deleteteing data in SAS between 2 dates

Hi,

 

Always post test data in the form of a datastep, this avoids us guessing what it looks like.  I assume than FDATE is a numeric SAS date here.  Also be aware there is no reverse of this procedure, be very sure that you a) want to delete from the dataset, b) know what you are deleting.  Again, I want you to be fully aware that this removes the data from the table in place - doesn't create a new dataset, deletes from that one - be very sure this is what you want to do!!

 

proc sql;
  delete from <your_dataset> 
  where FDATE between '01JAN2009'd and '31DEC2010'd;
quit;
Super User
Posts: 7,854

Re: Deleteteing data in SAS between 2 dates

Maxim 33: Intelligent data makes for intelligent programs!

 

Convert your text date variables to real SAS date variables, and the comparison will be a breeze:

 

data want;
set have;
where &start <= fdate <= &end;
run;

where &start and &end could contain SAS date literals or raw numerical values that correspond to the wanted cutoff dates.

The conversion would go like this:

data have;
set have (rename=(fdate=_fdate));
fdate = input(_fdate,ddmmyy10.);
format fdate ddmmyy10.;
drop _fdate;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: Deleteteing data in SAS between 2 dates

Posted in reply to KurtBremser

Hi Kurtbremser

 

Thank you for your help, I have converted the text date to date format but I think there is something wrong with my code as the results are coming back with no data. please see my code below.


data want; set have; where 01/01/2009 <= fdate <= 31/12/2010; run;
PROC Star
Posts: 283

Re: Deleteteing data in SAS between 2 dates

apply date constant "datemonthyear"d

Super User
Posts: 7,854

Re: Deleteteing data in SAS between 2 dates


zdassu wrote:

Hi Kurtbremser

 

Thank you for your help, I have converted the text date to date format but I think there is something wrong with my code as the results are coming back with no data. please see my code below.


data want; set have; where 01/01/2009 <= fdate <= 31/12/2010; run;

Oh, young Padawan, you need to study the secrets of SAS date and time values, before you can embark on the journey to mastership!

 

I specifically recommend https://support.sas.com/resources/papers/proceedings15/1334-2015.pdf

 

In short, to specify a date literal, you need to follow the structure "ddmmmyyyy"d, so your dates would be spelled as

"01jan2009"d and "31dec2010"d, respectively.

Note that dates are actually a simple number, counting the days from 01jan1960, in both directions (positive and negative).

Therefore, you could also use this:

data _null_;
start = mdy(1,1,2009);
call symputx('start_date',put(start,best.));
end = mdy(12,31,2010);
call symputx('end_date',put(end,best.));
run;

data want;
set have;
where &start_date <= fdate <= &end_date;
run;

If you also insert %put &start_date; somewhere in the code and look at the log, you will find that it contains the raw value, but just works as well.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: Deleteteing data in SAS between 2 dates

Posted in reply to KurtBremser

Hi kurt

I tried the code but I am getting an error in the log

 

ERROR: Syntax error while parsing WHERE clause.

ERROR 180-322: Statement is not valid or it is used out of proper order.

 

 

data want;
set have;
where &"01jan2009"d <= fdate <= &"31dec2010"d;
run;
Solution
‎05-09-2017 09:08 AM
Super User
Posts: 7,854

Re: Deleteteing data in SAS between 2 dates

& is a macro trigger, it has to be followed by a macro variable name, not by a value or literal. If you want to use macro variables, use my code AS IS.

If not, use the date literals without any additional characters.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: Deleteteing data in SAS between 2 dates

Posted in reply to KurtBremser
Thanks Kurt
I think I was getting a little confused, I was inserting the dates I didn't want to keep in the code, once I entered the dates I wanted to keep and ran the whole code together it worked. Thanks for all your help much appreciated
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 204 views
  • 0 likes
  • 4 in conversation