DATA Step, Macro, Functions and more

How to filter dates between two specific dates in SAS?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

How to filter dates between two specific dates in SAS?

Hello,

I have a large data set that I need to subset the data set between two specific dates. I would need to get the sales data only from 01/01/2005 to 12/31/2005.

Data set name: sales

variable name: sales_date

variable name: sold_item

variable name: quantity

------------------------------------------------------

sales_date | sold_item | quantity

12/24/2003 | printer | 233

11/10/2005 | printer | 130

07/24/2005 | phone | 256

09/26/2005 | ipad | 239

01/24/2006 | ipad | 542

01/17/2006 | notebook | 328

Thanks,

Rogerge


Accepted Solutions
Solution
‎01-16-2014 03:23 AM
Super User
Posts: 5,257

Re: How to filter dates between two specific dates in SAS?

Assuming that you data is in a SAS/RDBMS table, and the date are in date format, this is the way of formulating your filter:

where sales_date between '01Jan2005'd and '31dec2005'd

If your are commonly interested in whole years you cold try:

where year(sales_date) = 2005

But if your table is large, and the sales_date columns is indexed, using a function on that column will prevent the optimizer to use the index.

Data never sleeps

View solution in original post


All Replies
Contributor
Posts: 52

Re: How to filter dates between two specific dates in SAS?

Hello,

As per your requirement following Is the Solution. Pls let me know if I Am wrong.

data Chk;

  length sales_date sold_item $ 50;

  infile datalines dlm="|" dsd;

  input sales_date $  sold_item  $ quantity ;

  if scan(sales_date,3,"/") = "2005" then output;

  datalines;

12/24/2003 | printer | 233

11/10/2005 | printer | 130

07/24/2005 | phone | 256

09/26/2005 | ipad | 239

01/24/2006 | ipad | 542

01/17/2006 | notebook | 328

;

Run;

Regards,

Dishant Parikh

Solution
‎01-16-2014 03:23 AM
Super User
Posts: 5,257

Re: How to filter dates between two specific dates in SAS?

Assuming that you data is in a SAS/RDBMS table, and the date are in date format, this is the way of formulating your filter:

where sales_date between '01Jan2005'd and '31dec2005'd

If your are commonly interested in whole years you cold try:

where year(sales_date) = 2005

But if your table is large, and the sales_date columns is indexed, using a function on that column will prevent the optimizer to use the index.

Data never sleeps
Frequent Contributor
Posts: 114

Re: How to filter dates between two specific dates in SAS?

Hi Roger,

Have you tried with Proc sql with where clause includeing between and and.

like : where sales_date between  12/24/2003 and 07/24/2005

Regards

Uma Shanker Saini

New Contributor
Posts: 2

Re: How to filter dates between two specific dates in SAS?

This always works for me:

if '31Dec2005'd <= sales_date <= '01Jan2005'd;


Regards,

Senzo

Occasional Contributor
Posts: 19

Re: How to filter dates between two specific dates in SAS?

Hi!    You probably mean:     if '01Jan2005'd <= sales_date <= '31Dec2005'd;   / Br Anders

Anders Sköllermo
Ph.D., Swedish Actuary

"Both Age and IQ are 69+"
"Retired, But Not Tired"

Sandgränd 13, S-178 40 Ekerö
email: anders.skollermo@one.se
tele: 00468968419 mobil: 0046735077373
New Contributor
Posts: 2

Re: How to filter dates between two specific dates in SAS?

@AndersS

Yes I meant that, thanks.

Regular Contributor
Posts: 195

Re: How to filter dates between two specific dates in SAS?

Hi,

I assumed you need to subset the data based on regular interval and this interval might change over a period of time...so i belive rather than extract the specific year or date information, let's modified in such a way that you can subset based on any dates...All you need to change the dates in HAVE Dataset only...

data have;

  informat st_date ed_date mmddyy10.;

  input st_date ed_date;

  call symputx("st_date",st_date);

  call symputx("ed_date",ed_date);

  cards4;

01/01/2005

12/31/2005

;;;;

data want;

  set want;

  where sales_date between &st_date. and &ed_date.;

run;

-Urvish

Occasional Contributor
Posts: 11

Re: How to filter dates between two specific dates in SAS?

solution

 

 

data bank;
input @1 trans_dt date9. @13trans_amt comma7.;
format trans_dt date9. trans_amt comma8.;
datalines;
01jan2017 89,000
02feb2017 75,000
03mar2017 96,000
04apr2017 65,000
05may2017 10,000
06jun2017 25,000
07jul2017 36,000
08aug2017 32,000
09sep2017 46,000
10oct2017 50,000
11nov2017 31,000
;
run;

data trans;
set bank;
where trans_dt >= '01Jan2017'd and trans_dt<= '01apr2017'd
;
run;

proc sql;
create table trans2 as
select * from bank
where trans_dt between '01Jan2017'd and '31mar2017'd
;
quit;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 35938 views
  • 14 likes
  • 8 in conversation