Convert character date to numeric (dates are inconsistent)

[bobbyc]

I am trying to convert character dates to numeric date9. format. 

Some of the dates has only year, some has year-month, some has year-month-date as shown below

Chardate

-----------

2010-12-15

2010-10-04

2010-05

2011

2010-02-23

2011-04

numdate= input(chardate,yymmdd10.);

format numdate date9.;

If I use the above input function it is throwing error :  Invalid argument to function INPUT at line ...

Is there any other way to do this ?

Thanks

[Astounding · Accepted solution]

SAS dates always refer to a particular day.  So if you might be missing part of the date, the first step (before programming begins) is to decide what day you would like.

If you only have the year available, would you like SAS to use January 1 of that year?

If you only have year + month available, would you like SAS to use the 1st day of that month?

Once you have made those decisions, the programming will be very similar to what you have already written.

[Astounding]

If you are willing to go with 1st day of the year or month, here's a one-line replacement:

numdate = input( trim(chardate) || '-01-01', yymmdd10.);

[Reeza]

@Astounding That's clever 🙂

[RW9]

Edit: Removed as totally irrelevant.  I see what you mean, you use the implicit substr of the input so:

numdate = input( trim(chardate) || '-01-01', yymmdd10.);

Is the equivalent to:

numdate = input(substr(trim(chardate) || '-01-01',1,10), yymmdd10.);

[FreelanceReinh]

@RW9: At first glance I also thought this would be just the ordinary way to impute the year-only case, but wondered why Reeza would like it then. A second look revealed that the YYMMDD10. informat used with the INPUT function reads only the first 10 characters of the concatenated string, hence ignores the potential 11th, 12th, ... characters if the original character date had length >4. Finally, I checked that even informat YYMMDD16. is tolerant enough to extract the correct date from values such as '2010-12-15-01-01' or '2010-05-01-01'.

[FreelanceReinh]

Hi @bobbyc,

SAS date values are complete dates by nature. Given incomplete character dates, SAS does not impute missing components like day or month. [EDIT: The ANYDTxxx informats do perform imputations to some extent.] So, you have to develop rules what to do with incomplete dates (e.g. "if only the day is missing, set it to the 1st of the month"). Then, you can implement your rules by handling the character dates as strings first, perform the imputation of missing components, if any, and eventually apply the YYMMDD10. informat to the completed date string.

[Reeza]

You can also try anydtdte. but it still may not capture all cases. Is your data always YYYY-MM-DD?