07-30-2014 01:19 PM
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));
But this is not working . Any help appraciated.
07-30-2014 01:41 PM
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 ]
07-30-2014 01:47 PM
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?
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.
07-30-2014 03:09 PM
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.
07-30-2014 03:19 PM
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.
07-30-2014 03:34 PM
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));
07-30-2014 03:42 PM
Bingo! It worked. As per your suggestion changed from d to dt.
Thanks all this fixes my issue.
Need further help from the community? Please ask a new question.