topic Re: Filter SAS dataset using a where between two dates in data step between in SAS Procedures

Filter SAS dataset using a where between two dates in data step between

ahsyed — Wed, 30 Jul 2014 17:19:11 GMT

I have a dataset which needs to be filtered based upon two dates from and to. I am using the code:

DATA ABC (WHERE=(Date BETWEEN '01OCT2013:00:00:00'd AND '31DEC2013:00:00:00'd));

set Libname.ABC

But this is not working . Any help appraciated.

Thanks -

Re: Filter SAS dataset using a where between two dates in data step between

stat_sas — Thu, 05 Nov 2020 17:50:04 GMT

DATA ABC 
 (WHERE=(Date>='01OCT2013:00:00:00'dt 
     AND Date<='31DEC2013:00:00:00'dt)
 );
 set libname.ABC;
run;

Or use the BETWEEN keyword:

DATA ABC 
 (WHERE=(Date BETWEEN '01OCT2013:00:00:00'dt 
     AND '31DEC2013:00:00:00'dt)
 );
 set libname.ABC;
run;

When you place the WHERE option on the data set in the DATA keyword, it limits the output to just the filtered values, but the DATA step will read all of the values in the data on the SET statement. To limit the records you read, place the filter on the SET statement:

DATA ABC;
 set libname.ABC (WHERE=
   (Date BETWEEN '01OCT2013:00:00:00'dt 
      AND '31DEC2013:00:00:00'dt)
  );
run;

Re: Filter SAS dataset using a where between two dates in data step between

ahsyed — Wed, 30 Jul 2014 17:41:39 GMT

Thanks for the solution. The Code works however there is no data coming out for the datastep.

as a background for the date: I am getting the date from

Date = input(Substr(CC,1,10),anydtdtm20.); *(CC is the date with string);

FORMAT Date datetime19.;

Though it is showing date cloumn without filter, when I am filtering the dataset upon date to be between 01OCT2013'd AND '31DEC2013'd, it is not shouing up.[ Any Clue ]

Thanks

Re: Filter SAS dataset using a where between two dates in data step between

jwillis — Wed, 30 Jul 2014 17:47:49 GMT

ahsyed,

I'm responding because I have never seen your style of coding. It is intriguing. Could your issue be that your date is "datetime19." and your where values are only 18 positions long?

01OCT2013:00:00:00

I've seen a where in the set statement and I've seen a where after the set statement but until now, I have never seen a where in the Data statement. Cool.

Re: Filter SAS dataset using a where between two dates in data step between

ahsyed — Wed, 30 Jul 2014 19:09:14 GMT

Hi jwillis

I tried chnanging the code to 18 position. still I am not able to see the output. Looks like once the data is in date format, it is not allowing 'WHERE' Statement to filter based upon the date.

I have to change it to date from varchar to get the right number of records when I filter the data based upon date. However converting to date and filtering the dates between two given dates is actully not getting any outputs.

Thanks -

Re: Filter SAS dataset using a where between two dates in data step between

jwillis — Wed, 30 Jul 2014 19:19:32 GMT

Ahsyed,

I am still intrigued. I have written date selection algorithm's that also failed. Inevitably the issue cause was between character representations of dates; blanks; SAS date numerics; and format settings. Can you post ten examples of your date values and what you expect to retreive from the ten rows when your where statement is working correctly? Your code is pulling 10 characters with a substring then formatting the 10 characters as anydttm20. I've never seen anydttm20. :smileyblush: and I want to play around and learn more about what you are doing.

Re: Filter SAS dataset using a where between two dates in data step between

stat_sas — Wed, 30 Jul 2014 19:34:09 GMT

Hi,

Try this, just added dt instead of d in the syntax.

DATA ABC (WHERE=(Date>='01OCT2013:00:00:00'dt AND Date<='31DEC2013:00:00:00'dt));

set Libname.ABC

Re: Filter SAS dataset using a where between two dates in data step between

ahsyed — Wed, 30 Jul 2014 19:42:23 GMT

stat@sas

Bingo! It worked. As per your suggestion changed from d to dt.

Thanks all this fixes my issue.

Thanks

ahsyed