Character to Numeric - Date conversion

[Babloo]

I'm trying to create the numeric variable called PODVER using the following case when in DI Studio. But I'm not sure why I'm getting missing values in PODVER. NEW_RENEWAL_DAY_MONTH_YEAR and CURRENT_DATE are numeric variables and it has values like 20SEP and 20SEP2018 respectively. Please be informed that I'm applying the format DATE9. in the new variable PODVER.

case when NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon  and KNKATD='7'  then REPORTING_DATE_ddmonyyyy 
         when input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.) >CURRENT_DATE then input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2018"),date9.) 
         else input(cats(NEW_RENEWAL_DAY_MONTH_YEAR,"2019"),date9.)
end

Please guide me to resolve the issue.

[RW9]

Please refer to the guidance on posting a question and review what you post.  There are several posts on this topic now.  You state that:

"NEW_RENEWAL_DAY_MONTH_YEAR and CURRENT_DATE are numeric variables and it has values like 20SEP and 20SEP2018"

A numeric variable cannot contain a text string: "20SEP", in fact a numeric variable (date) also cannot contain 20SEP, although a full numeric date can be formatted to display on day and month.

Please start by providing us with some text data which accurately shows what your data is and how it looks, particulalrly in a datastep so that we can clearly see formats, types etc.     

Unfortunately I cannot debug anything from what you have posted as it all contradicts each other, this for instance:

NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon 

 How can a day/month/year variable ever be equal to a ddmon variable, and how can a ddmon variable be a date - which can only be all parts-ddmon and yyyy.

Start with test data in the form of a datastep - data which matches what you have for all variables in question.

[s_lassen · Accepted solution]

If your date variables are numeric, then the input(cats(...)) construct won't work as intended. You are not getting any error messages because CATS automatically translates numeric to character, but it does so using the BEST32. format.

What you want to do is probably something like this:

case when NEW_RENEWAL_DAY_MONTH_YEAR=REPORTING_DATE_ddmon  and KNKATD='7'  then REPORTING_DATE_ddmonyyyy 
         when mdy(month(NEW_RENEWAL_DAY_MONTH_YEAR),day(NEW_RENEWAL_DAY_MONTH_YEAR),2018) >CURRENT_DATE then mdy(month(NEW_RENEWAL_DAY_MONTH_YEAR),day(NEW_RENEWAL_DAY_MONTH_YEAR),2018)
         else mdy(month(NEW_RENEWAL_DAY_MONTH_YEAR),day(NEW_RENEWAL_DAY_MONTH_YEAR),2019)
end