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
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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