/* 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 ?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: