Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

     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 -


Accepted Solutions
Solution
‎07-30-2014 01:25 PM
Trusted Advisor
Posts: 1,230

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

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

set Libname.ABC

View solution in original post


All Replies
Solution
‎07-30-2014 01:25 PM
Trusted Advisor
Posts: 1,230

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

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

set Libname.ABC

New Contributor
Posts: 4

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

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

Regular Contributor
Posts: 217

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

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.

New Contributor
Posts: 4

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

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 -

Regular Contributor
Posts: 217

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

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.

Trusted Advisor
Posts: 1,230

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

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

New Contributor
Posts: 4

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

Apprentice

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

Thanks all this fixes my issue.

Thanks

ahsyed

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 8376 views
  • 9 likes
  • 3 in conversation