I want to find a date after today's date

[Sandeep77]

Hi all,

I am trying to find only those records which have new_date after today's date. I have tried a code but it shows all the records from the previous table and not the records which has new_date after today's date. Please suggest. I want the output data to show only those records which have new_date after today. Here is the sample data:

Data Delayed Accounts;
infile cards expandtabs;
input debt_code rep_code$ dt_delaydays dt_datdelay:date9. new_date :date9.;
format dt_datdelay date9. new_date :date9.;
datalines ;
422555961 122 6 20NOV2023:00:00:00.000 26NOV2023:00:00:00.000
339877599 168 5 24JUL2020:00:00:00.000 29JUL2020:00:00:00.000
344681317 168C 5 12FEB2020:00:00:00.000 17FEB2020:00:00:00.000
332355999 168 4 23FEB2021:00:00:00.000 27FEB2021:00:00:00.000
308007913 133 30 05AUG2019:00:00:00.000 04SEP2019:00:00:00.000
run;

proc sql; 
create table delayed_accounts_new as 
select * 
from delayed_accounts 
where new_date > today(); 
quit;
Log:
30         proc sql;
31         create table delayed_accounts_new as
32         select *
33         from delayed_accounts
34         where new_date > today();
NOTE: Compressing data set WORK.DELAYED_ACCOUNTS_NEW decreased size by 19.63 percent. 
      Compressed is 176 pages; un-compressed would require 219 pages.
NOTE: Table WORK.DELAYED_ACCOUNTS_NEW created, with 254954 rows and 5 columns.

35         quit;

[PaigeMiller]

Your code looks like it should work properly. It works for me on your sample data.

[Sandeep77]

It works fine but it shows all the record from the delayed_accounts table. I want only those records which have new_date after today's date.

[PaigeMiller]

---

@Sandeep77 wrote:

It works fine but it shows all the record from the delayed_accounts table. I want only those records which have new_date after today's date.

---

Your code works properly on the sample data you show.

Perhaps the sample data does not really represent your actual data?

[Sandeep77]

Hi @PaigeMiller 
That is my real data. Let me explain with an example. If you see the first row the new_date is after today's date which is 21/11/23. The first row shows new_date as 26/11/23. Rest of all the dates in new_date is before today's date. I want only those records which are after today's date (21/11/23). So from the sample data, it should show only the first record. But when I run my code, it includes all the dates from previous table (before today's date and after today's date).

[PaigeMiller]

---

@Sandeep77 wrote:

That is my real data. 

---

I am extremely skeptical. It is much more likely that you have date/time values instead of date values. If you really have date values, your code will work. 

[Sandeep77]

Hi @PaigeMiller , My data is a datetime value (datetime22.3). For eg 12APR2023:00:00:00.000. Is that what you are asking?

[PaigeMiller · Accepted solution]

---

@Sandeep77 wrote:

Hi @PaigeMiller , My data is a datetime value (datetime22.3). For eg 12APR2023:00:00:00.000. Is that what you are asking?

---

So then your sample data does not represent your actual data. In your sample data, new_date is a date value. Now you are saying it is a datetime value.

The code you need is: 

where datepart(new_date) > today(); 

[Sandeep77]

@PaigeMiller. Thanks, it worked. I am not very good with the dates in SAS. Thanks

[Kurt_Bremser]

Run PROC CONTENTS on your real data and post the result. There's no sense in trying to help you further unless you do this.

If this should reflect your real data, then your DATA step has to look like this:

data delayed_accounts; 
infile cards; 
input debt_code rep_code $ dt_delaydays dt_datdelay :date9. new_date :date9.;
format dt_datdelay new_date date9.; 
datalines; 
422555961 122 6 20NOV2023 26NOV2023
339877599 168 5 24JUL2020 29JUL2020
344681317 168C 5 12FEB2020 17FEB2020
332355999 168 4 23FEB2021 27FEB2021
308007913 133 30 05AUG2019 04SEP2019
;

just for clarity.

Then your code

proc sql; 
create table delayed_accounts_new as 
select * 
from delayed_accounts 
where new_date > today(); 
quit;

will work, as the log clearly indicates:

 69         proc sql;
 70         create table delayed_accounts_new as
 71         select *
 72         from delayed_accounts
 73         where new_date > today();
 NOTE: Table WORK.DELAYED_ACCOUNTS_NEW created, with 1 rows and 5 columns.

[Kurt_Bremser]

If, in your real data, new_date is not a date but a datetime, your code can't work as intended.

Maxim 3: Know Your Data.

Also remember the stuff I told you to write down 100 times?