Finding dates between two dates not working

Question

I have a dataset where date_occur is in MMDDYY10 format (looks like 10/23/2014). I want all dates in FY17 which is 10/01/2016-09/30/2017. The following code is not working for some reason. Why!!

I'm not getting any error or warning messages I'm just getting an empty table.

Thanks in advance

data fy17;
set y16_17;
where date_occur between 10/01/2016 and 09/30/2017;
run;

The Log says: NOTE: There were 0 observations read from the data set WORK.Y16_17. WHERE (date_occur>=0.0001487357 and date_occur<=0.0049603175);

I'm thinking SAS is not understanding that date_occur is in mmddyy10 format. The internet has code like

where date_occur between '10/01/2016'd and '09/30/2017'd;

I tried this and it did not work either.

Answer 1

Your 'dates' are being interpreted as numeric expressions, and none of the date values in your table lie in the resulting range. Use date literals instead:

data fy17;
set y16_17;
where date_occur between '01oct2016'd and '30sep2017'd;
run;

You can only use date. format (ddmmmyyyy / ddmmmyy) when specifying date literals.