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 -
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;
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;
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
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.
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 -
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.
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
Bingo! It worked. As per your suggestion changed from d to dt.
Thanks all this fixes my issue.
Thanks
ahsyed
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.