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 |
& 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.
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;
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;
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;
apply date constant "datemonthyear"d
@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.
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;
& 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.