topic Re: Deleteteing data in SAS between 2 dates in SAS Programming

Deleteteing data in SAS between 2 dates

zdassu — Tue, 09 May 2017 09:54:58 GMT

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

mpr	ReadDate	Source	reason	rej1	rej2	rej3	rej4	fname	fdate
100	20101210	E	N	aaa	bbb			ABC	13/12/2010

Re: Deleteteing data in SAS between 2 dates

RW9 — Tue, 09 May 2017 10:04:32 GMT

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;

Re: Deleteteing data in SAS between 2 dates

Kurt_Bremser — Tue, 09 May 2017 10:06:13 GMT

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;

Re: Deleteteing data in SAS between 2 dates

zdassu — Tue, 09 May 2017 11:06:30 GMT

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;

Re: Deleteteing data in SAS between 2 dates

novinosrin — Tue, 09 May 2017 11:10:02 GMT

apply date constant "datemonthyear"d

Re: Deleteteing data in SAS between 2 dates

Kurt_Bremser — Tue, 09 May 2017 11:17:53 GMT

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

Re: Deleteteing data in SAS between 2 dates

zdassu — Tue, 09 May 2017 12:19:13 GMT

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;

Re: Deleteteing data in SAS between 2 dates

Kurt_Bremser — Tue, 09 May 2017 12:27:06 GMT

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

Re: Deleteteing data in SAS between 2 dates

zdassu — Tue, 09 May 2017 13:08:26 GMT

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