how to convert text field(32767) to date field with date9. format?

[Stalk]

I have two fields datesent (Ex: 2022-01-13) is text field with length 32767 and collection_date(Ex:22JAN2022) is number with Date9. format
would like to compare and get line list of records where datesent < collection_date

Tried the following to convert datesent but it's not working..

data temp2;
set temp;
format datesent1 date9.;
datesent1 = input ( datesent, date9.);
run;

[ballardw · Accepted solution]

If datesent is indeed like 2022-01-13 then you want a YYMMDD10. informat not Date9. Input uses the layout of the field, not how you think you want it to look to read the data.

datesent1 = input ( datesent, yymmdd10.);

I am a little concerned about the datesent is 32K characters though. If any of the extra characters come before the date bit then you need to remove them as leading spaces would be significant and might require

datesent1 = input ( strip(datesent), yymmdd10.);

[Cynthia_sas]

Hi:

  With the INPUT function, you need to use an INFORMAT that matches the character string. You want the converted number to use the DATE9. format. But you want your character string to be converted using the yymmdd10. INFORMAT. Otherwise, my guess is that you are seeing error messages in the log like: NOTE: Invalid argument to function INPUT at line xxx column yy.

  Just remember that you are essentially converting the character string from how it looks as a date (yymmdd10.) into a number that is the number of days since Jan 1, 1960. That converted number should be displayed with the DATE9. format. So your FORMAT statement is OK. Your INFORMAT in the INPUT function just needs to be fixed.

Cynthia

[Stalk]

Cynthia, You are right about the Note. Followed your suggestion and made the changes. It worked.
Thank you both