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

How to filter dates between two specific dates in SAS?

Roger — Thu, 16 Jan 2014 06:02:57 GMT

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

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

dishant — Thu, 16 Jan 2014 06:36:26 GMT

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

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

LinusH — Thu, 16 Jan 2014 08:23:06 GMT

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.

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

umashankersaini — Thu, 16 Jan 2014 08:31:50 GMT

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

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

Senzo_DL — Thu, 16 Jan 2014 09:16:22 GMT

This always works for me:

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

Regards,

Senzo

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

AndersS — Sat, 18 Jan 2014 17:40:39 GMT

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

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

Senzo_DL — Thu, 23 Jan 2014 12:33:46 GMT

@AndersS

Yes I meant that, thanks.

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

UrvishShah — Mon, 27 Jan 2014 12:35:03 GMT

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

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

Sharad_Pujari — Thu, 20 Jul 2017 07:37:54 GMT

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;