BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Roger
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

8 REPLIES 8
dishant
Calcite | Level 5

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
Tourmaline | Level 20

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
umashankersaini
Quartz | Level 8

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
Calcite | Level 5

This always works for me:

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


Regards,

Senzo

AndersS
Lapis Lazuli | Level 10

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

Anders Sköllermo (Skollermo in English)
Senzo_DL
Calcite | Level 5

@AndersS

Yes I meant that, thanks.

UrvishShah
Fluorite | Level 6

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
Calcite | Level 5

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 190837 views
  • 18 likes
  • 8 in conversation