topic Re: Character to date conversion - when with missing date components in SAS Procedures

Character to date conversion - when with missing date components

Solph — Wed, 26 Sep 2012 16:11:36 GMT

I know I can use INPUT to convert a character variable to a date variable. But what if one of the date components is missing or out of range. Here is an example with some of the wrong day component value. Is there an easy way or a function to tell SAS to replace the out of range or missing as say day 1 or day 31 or any day?

Thanks.

data aa; input id date $;

datalines;

1 20110304

2 20090405

3 20100399

4 20100300

5 201004

;

run;

data bb; set aa;

format newdate yymmdd10.;

newdate=input(date, yymmdd10.);

proc print noobs; run;

id date newdate (Ideal output)

1 20110304 2011-03-04

2 20090405 2009-04-05

3 20100399 . 2010-03-01 (or 2010-03-15, or 2010-03-31)

4 20100300 . 2010-03-01

4 201004 1920-10-04 2010-04-01

Re: Character to date conversion - when with missing date components

MikeZdeb — Wed, 26 Sep 2012 16:30:17 GMT

hi ... if it's always a problem with just the day part (out of range or missing) ...

data bb;

set aa;

newdate = input(catt(date,'00'), ?? yymmdd8.);

if missing(newdate) then

newdate = mdy(input(substr(date,5,2),2.), 1 , input(substr(date,5,2),2.));

format newdate yymmdd10.;

run;

id date newdate

1 20110304 2011-03-04

2 20090405 2009-04-05

3 20100399 2003-03-01

4 20100300 2003-03-01

5 201004 2004-04-01

Re: Character to date conversion - when with missing date components

PGStats — Wed, 26 Sep 2012 16:46:27 GMT

Something like :

newdate=mdy(

input(substr(date,5,2),2.),

max(1,min(31,input(coalescec(substr(date,7,2),"01"),2.))),

input(substr(date,1,4),4.) );

Re: Character to date conversion - when with missing date components

PGStats — Wed, 26 Sep 2012 16:59:32 GMT

Hi Mike, nice approach! I guess you meant

newdate = mdy(input(substr(date,5,2),2.), 1 , input(substr(date,1,4),4.));

Re: Character to date conversion - when with missing date components

Linlin — Wed, 26 Sep 2012 16:59:46 GMT

Hi Mike,

I made changes to your code.

data bb;

set aa;

newdate = input(catt(date,'00'), ?? yymmdd8.);

if missing(newdate) then

newdate = mdy(input(substr(date,5,2),2.), 1 , input(substr(date,1,4),4.));

format newdate yymmdd10.;

run;

proc print;run;

Re: Character to date conversion - when with missing date components

Astounding — Wed, 26 Sep 2012 17:42:30 GMT

OK, as long as we are working on each other's code, consider this:

input(substr(date,1,4),4.)

It could be replaced with:

input(date,4.)

Good luck all.

Re: Character to date conversion - when with missing date components

MikeZdeb — Wed, 26 Sep 2012 18:01:12 GMT

hi ... thanks, so much for my cutting/pasting my own code

also the CATT function part should have had '01' ... the FINAL version (using Astounding's idea about the year portion)

data bb;

set aa;

newdate = input(catt(date,'01'), ?? yymmdd8.);

if missing(newdate) then

newdate = mdy(input(substr(date,5,2),2.), 1 , input(date,4.));

format newdate yymmdd10.;

run;

Re: Character to date conversion - when with missing date components

data_null__ — Wed, 26 Sep 2012 18:19:06 GMT

The ISO 8601 INFORMATS can read partial 8601 dates but you have to get ride of that silly 99 and 00.

data aa; 
   input id date $;
   if length(date) eq 8 and substr(date,7,2) in('00','99') then substr(date,7)='  '; 
   iso = input(date,B8601DA8.);
   format iso yymmddn.; 
   datalines; 
1  20110304
8  2011
2  20090405
3  20100399
4  20100300
5  201004
;;;;
   run; 

Re: Character to date conversion - when with missing date components

Solph — Wed, 26 Sep 2012 19:43:28 GMT

Thanks all. I just tested them all and they all worked.

Regarding data_null_'s solution, Does SAS have a similar function like B8601DA8. to handle the month part being blank?

Re: Character to date conversion - when with missing date components

data_null__ — Wed, 26 Sep 2012 19:57:05 GMT

Yes see my example. When month is blank the date is set to first day of the year.

Re: Character to date conversion - when with missing date components

Solph — Wed, 26 Sep 2012 20:10:36 GMT

With your example, it worked great. But I added another case with "2010 31", it returned with a missing iso value for that record. Any solution?

data aa;

input id @4 date $char8.;

if length(date) eq 8 and substr(date,7,2) in('00','99') then substr(date,7)=' ';

iso = input(date,B8601DA8.);

format iso yymmddn.;

datalines;

1 20110304

8 2011

9 2010 31

2 20090405

3 20100399

4 20100300

5 201004

;;;;

run;

proc print; run;

Re: Character to date conversion - when with missing date components

data_null__ — Wed, 26 Sep 2012 20:30:19 GMT

If you don't know the month how can the day be useful. The ISO 8601 standard only alows truncation from the right.

Re: Character to date conversion - when with missing date components

MikeZdeb — Wed, 26 Sep 2012 22:55:47 GMT

hi ... you can add the TRANWRD function to the data step if you want a blank month as January and a blank day as the 1st of the month ...

if you do that, then you can revert to YYMMDD8. if you also correct the 00 and 99

data aa;

input id @4 date $char8.;

if length(date) eq 8 and substr(date,7,2) in('00','99') then substr(date,7)=' ';

date = tranwrd(date,' ','01');

newdate = input(date,yymmdd8.);

format iso yymmddn.;

datalines;

1 20110304

8 2011

9 2010 31

2 20090405

3 20100399

4 20100300

5 201004

;

Re: Character to date conversion - when with missing date components

Solph — Thu, 27 Sep 2012 08:42:10 GMT

Many thanks Mike. Indeed the code works both ways (with either day or month, or both, missing).

> If you don't know the month how can the day be useful.

Data _null, it's just that the date variable wasn't properly processed as a date variable, the integrity of year/month/day isn't always checked, and we'd rather have proxy date values than assign them as missing simply because some date components are missing.

Again thanks everyone for your extremely helpful answers (helped me to see how it can be resolved differently).

Re: Character to date conversion - when with missing date components

Linlin — Thu, 27 Sep 2012 12:35:23 GMT

Hi Solph,

I am curious why you always check your questions as "Assumed Answered"?