BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zdassu
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Kurt_Bremser
Super User

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;
zdassu
Quartz | Level 8

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;
novinosrin
Tourmaline | Level 20

apply date constant "datemonthyear"d

Kurt_Bremser
Super User

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

zdassu
Quartz | Level 8

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;
Kurt_Bremser
Super User

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

zdassu
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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