Partial date query

[pdhokriya]

/* My requirement final data in new variable should consist of blank records and less then "2020" , "2020-10" , "2020-10-09" */

data abc ;
date = "2020-10-09"; output ;
date = "2009"; output ;
date = "2020-08"; output ;
date = "2020-09"; output ;
date = "2020-10"; output ;
date = "2020-10-08"; output ;
date = ""; output ;
date = "2020-10-07"; output ;
date = "2020"; output ;
run;

/******This is my approch which I am not satisfied with.*******/

proc sql ;
create table ck as
select * from (select date,
case when length (date) = 4 and date < "2020" then date
when length (date) = 7 and date < "2020-10" then date
when length (date) = 10 and date < "2020-10-09" then date
when date = "" then ""
else "invalid"
end as xyz
from abc ) where xyz ne "invalid" ;
quit;

Thank you in advance.

Take care

[PaigeMiller]

Please state why you are unhappy with the results. Show us the desired results.

[pdhokriya]

I am happy with result , i am not happy with my program.

[RichardDeVen]

The question is a string comparison question and only involves strings that look like calendar values.

Perhaps this will help

proc sql ;
create table ck as
select * from (
  select date,
    case 
      when length (date) =  4 and date < "2020"       then "< 2020"
      when length (date) =  7 and date < "2020-10"    then "< 2020-10"
      when length (date) = 10 and date < "2020-10-09" then "< 2020-10-09"
      when date = "" then ""
      else "invalid"
    end as xyz
    from abc
) get_me_out_of_this_asylum
where xyz ne "invalid" ;
quit;

[pdhokriya]

This is not desired output. Check my program output data.

[RichardDeVen]

Why does the query have a sub-select ? Is that what you are unhappy with ?  What streamlined query do you intuitively feel could be written ?

[pdhokriya]

I was looking without "invalid" data. filtering of invalid data which is annoying to me. 🙂