Help using Base SAS procedures

Character to date conversion - when with missing date components

Reply
Frequent Contributor
Posts: 101

Character to date conversion - when with missing date components

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

Valued Guide
Posts: 765

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

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

Respected Advisor
Posts: 4,659

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

Hi Mike, nice approach! I guess you meant

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

PG

PG
Valued Guide
Posts: 765

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

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;

Super Contributor
Posts: 1,636

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

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;

Super User
Posts: 5,096

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

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.

Respected Advisor
Posts: 4,659

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

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

PG

PG
Respected Advisor
Posts: 3,777

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

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;
Frequent Contributor
Posts: 101

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

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?

Respected Advisor
Posts: 3,777

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

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

Frequent Contributor
Posts: 101

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

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;

Respected Advisor
Posts: 3,777

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

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

Valued Guide
Posts: 765

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

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

;

Frequent Contributor
Posts: 101

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

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

Super Contributor
Posts: 1,636

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

Hi Solph,

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

Ask a Question
Discussion stats
  • 14 replies
  • 1073 views
  • 0 likes
  • 6 in conversation