how to handle missing date

[mhinchy]

I have the following dataset

data have;
input date_of_birth;
datalines;
19600101
190002..

200002..

199812..

;
run;

I want to write a program that will create a new dataset, that replaces ‘..’ by the last day of the respective month.

Note: I also want to account for leap years. Therefore, for the 2nd value (190002..), I would want this to read 19000229 because this is a Leap Year

Appreciate all help!

[Reeza · Accepted solution]

Read First 6 chars only as a char, convert it to a date using input and correct informat and then use INTNX to set it to the end of the month with the alignment option. 

Data want;

input date $6.;

date_end = intnx('month', input(date, yymm6.), 0, 'e');

format date_end yymmdd8.;

cards;

.....

[Jagadishkatam]

@Reeza solution works fine, the only change is we need to use the informat yymmn6.

[Jagadishkatam]

data have;
infile datalines missover;
input date_of_birth $10.;
datalines;
19600101
190002
200002
199812
;
run;

data want;
set have;
month=substr(date_of_birth,5,2);
year=substr(date_of_birth,1,4);
day=substr(date_of_birth,7,2);


length date date_imp $50.; 
 
    if day='' then do;  
date=cats(strip(year),strip(put(input(month, best.),z2.)));  
if month="12" then date_imp=compress(put((mdy(1,1,input(year,best.)+1)-1), is8601da.),'-'); 
else date_imp=compress(put((mdy(input(month,best.)+1,1,input(year,best.))-1),is8601da.),'-');    
end;    
else do;       
date=compress(put(mdy(input(month,best.),input(day,best.),input(year,best.)),is8601da.),'-');  
date_imp=compress(date,'-');    
end;
run;

[art297]

data have;
  input cdate_of_birth $;
  format date_of_birth date9.;
  cdate_of_birth=compress(cdate_of_birth,,'kd');
  if length(cdate_of_birth) lt 8 then date_of_birth=
   intnx('month',input(catt(cdate_of_birth,'01'),yymmdd8.),0,'e');
  else date_of_birth=input(cdate_of_birth,yymmdd8.);
  datalines;
19600101
190002..
200002..
199812..
;

Art, CEO, AnalystFinder.com