DATA Step, Macro, Functions and more

Convert character date to numeric (dates are inconsistent)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Convert character date to numeric (dates are inconsistent)

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

 


Accepted Solutions
Solution
‎02-24-2016 04:17 PM
Super User
Posts: 5,516

Re: Convert character date to numeric (dates are inconsistent)

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.

View solution in original post


All Replies
Solution
‎02-24-2016 04:17 PM
Super User
Posts: 5,516

Re: Convert character date to numeric (dates are inconsistent)

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.

Super User
Posts: 5,516

Re: Convert character date to numeric (dates are inconsistent)

Posted in reply to 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.);

 

 

Super User
Posts: 19,869

Re: Convert character date to numeric (dates are inconsistent)

Posted in reply to Astounding

@Astounding That's clever Smiley Happy

Super User
Super User
Posts: 7,993

Re: Convert character date to numeric (dates are inconsistent)

[ Edited ]
Posted in reply to Astounding

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.);

Trusted Advisor
Posts: 1,118

Re: Convert character date to numeric (dates are inconsistent)

@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'.

Trusted Advisor
Posts: 1,118

Re: Convert character date to numeric (dates are inconsistent)

[ Edited ]

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.

Super User
Posts: 19,869

Re: Convert character date to numeric (dates are inconsistent)

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 789 views
  • 12 likes
  • 5 in conversation