DATA Step, Macro, Functions and more

how to handle missing date

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

how to handle missing date

 

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!


Accepted Solutions
Solution
‎05-13-2017 09:31 PM
Super User
Posts: 19,767

Re: how to handle missing date

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;

.....

 

View solution in original post


All Replies
Solution
‎05-13-2017 09:31 PM
Super User
Posts: 19,767

Re: how to handle missing date

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;

.....

 

Trusted Advisor
Posts: 1,137

Re: how to handle missing date

@Reeza solution works fine, the only change is we need to use the informat yymmn6.
Thanks,
Jag
Trusted Advisor
Posts: 1,137

Re: how to handle missing date

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;
Thanks,
Jag
PROC Star
Posts: 7,467

Re: how to handle missing date

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

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 186 views
  • 2 likes
  • 4 in conversation