BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sandeep77
Lapis Lazuli | Level 10

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

 


@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(); 
--
Paige Miller

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

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
Diamond | Level 26

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

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

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
Diamond | Level 26

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

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

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

PaigeMiller
Diamond | Level 26

 


@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(); 
--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10
@PaigeMiller. Thanks, it worked. I am not very good with the dates in SAS. Thanks
Kurt_Bremser
Super User

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

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?

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 404 views
  • 1 like
  • 3 in conversation