BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ahsyed
Calcite | Level 5

     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 -

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13
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;

View solution in original post

7 REPLIES 7
stat_sas
Ammonite | Level 13
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;
ahsyed
Calcite | Level 5

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

jwillis
Quartz | Level 8

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.

ahsyed
Calcite | Level 5

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 -

jwillis
Quartz | Level 8

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.

stat_sas
Ammonite | Level 13

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

ahsyed
Calcite | Level 5

Apprentice

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

Thanks all this fixes my issue.

Thanks

ahsyed

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!

What is Bayesian Analysis?

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.

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
  • 7 replies
  • 58934 views
  • 10 likes
  • 3 in conversation