topic Re: Partial date query in SAS Programming

Partial date query

pdhokriya — Wed, 25 Nov 2020 11:06:04 GMT

/* 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

Re: Partial date query

PaigeMiller — Wed, 25 Nov 2020 11:35:33 GMT

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

Re: Partial date query

RichardDeVen — Wed, 25 Nov 2020 13:16:10 GMT

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;

Re: Partial date query

pdhokriya — Fri, 27 Nov 2020 14:54:33 GMT

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

Re: Partial date query

pdhokriya — Fri, 27 Nov 2020 14:56:35 GMT

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

Re: Partial date query

RichardDeVen — Fri, 27 Nov 2020 17:38:06 GMT

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 ?

Re: Partial date query

pdhokriya — Sat, 28 Nov 2020 02:50:06 GMT

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