/* 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
Please state why you are unhappy with the results. Show us the desired results.
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;
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 ?
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.