How to filter dates between two specific dates in SAS?

[Roger]

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

[dishant]

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

[LinusH · Accepted solution]

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.

[umashankersaini]

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

[Senzo_DL]

This always works for me:

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

Regards,

Senzo

[AndersS]

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

[Senzo_DL]

@AndersS

Yes I meant that, thanks.

[UrvishShah]

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

[Sharad_Pujari]

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;