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
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.
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
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.
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
This always works for me:
if '31Dec2005'd <= sales_date <= '01Jan2005'd;
Regards,
Senzo
Hi! You probably mean: if '01Jan2005'd <= sales_date <= '31Dec2005'd; / Br Anders
@AndersS
Yes I meant that, thanks.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.