DATA Step, Macro, Functions and more

date impute

Accepted Solution Solved
Reply
Regular Contributor
Posts: 190
Accepted Solution

date impute

[ Edited ]

am writing dis code to check missing day & month  from the input below  & display the correct format according to the given values below,  the dates should be converted to ddmmmyyyy date9 format year is constant,  if day is missing then day=11 if month mssng then month=JUL 

but am getting a different output plz can u help below is my code & output.

 

 

data Dte;
input birthdate $10.;
cards;
Jan1975
1977
021978
run;
 
data IMPUTE(Keep = birthdate new_bdate);
set dte; 
format day $2. month $3. New_bdate $11.;
call scan(birthdate, 1, First_Pos, First_Length);
First_Word = substrn(birthdate, First_Pos, First_Length);
call scan(birthdate, 1, Last_Pos, Last_Length);
Last_Word = substrn(birthdate, Last_Pos, Last_Length);
if length(birthdate) = 10 then length1 = 'Y';
else length1 = 'N';
if length1 = 'Y' then do;
Day = substr(birthdate,1,2);
Month = substr(birthdate,3,3);
end;
else if length1 = 'N' then do;
if (First_Pos = '1' and Last_Length = '2') then do 
Day = First_Word;
Month = 'JUL';

end;
else if (First_Pos = '1' and Last_Length = '8') then do 
Day = First_Word;
Month = Last_Word;

end;
else if (First_Pos = '1' and Last_Length = '4') then do 
Day = First_Word;
Month = 'JUL';
Year = Last_Word;
end;
if (First_Pos = '2' and Last_Length = '5') then do 
Day = '11';
Month = First_Word;

end;
else if (First_Pos = '3' and Last_Length = '4') then do 
Day = '11';
Month = First_Word;
Year = Last_Word;
end;
else if (First_Pos = '3' and Last_Length = '4') then do 
Day = '11';
Month = 'JUL';
Year = Last_Word;
end;
end;
New_bdate = Day||Month||Year;
run;

 

output-->

 
birthdate
 
New_bdate
 
1 Jan1975  
2 1977 19JUN1977
3 021978  

 

HOW can i modify this code to get required output.

 

 

 


Accepted Solutions
Solution
‎02-25-2016 02:05 AM
Super User
Super User
Posts: 7,413

Re: date impute

It depends a bit, you may be able to do it just on length:

data have;
  birthdate="Jan1975"; output;
  birthdate="1977"; output;
  birthdate="021978"; output;
run;

data want;
  set have;
  select(length(strip(upcase(birthdate))));
    when (9) new_bdate=input(birthdate,date9.);
    when (7) new_bdate=input(cats("11",birthdate),date9.);
    when (4) new_bdate=input(cats("11JUL",birthdate),date9.);
    otherwise;
  end;
  format new_bdate date9.;
run;

Not sure what to do with the last value you gave, there is no indication of what 021978 represents.  Where is the data coming from, they should have specifications which detail the format of the data, just add where clause for each permutation of the data.

View solution in original post


All Replies
Solution
‎02-25-2016 02:05 AM
Super User
Super User
Posts: 7,413

Re: date impute

It depends a bit, you may be able to do it just on length:

data have;
  birthdate="Jan1975"; output;
  birthdate="1977"; output;
  birthdate="021978"; output;
run;

data want;
  set have;
  select(length(strip(upcase(birthdate))));
    when (9) new_bdate=input(birthdate,date9.);
    when (7) new_bdate=input(cats("11",birthdate),date9.);
    when (4) new_bdate=input(cats("11JUL",birthdate),date9.);
    otherwise;
  end;
  format new_bdate date9.;
run;

Not sure what to do with the last value you gave, there is no indication of what 021978 represents.  Where is the data coming from, they should have specifications which detail the format of the data, just add where clause for each permutation of the data.

Regular Contributor
Posts: 190

Re: date impute

it represents ddyyyy with month missing
Super User
Super User
Posts: 7,413

Re: date impute

So add another condition to handle this - remember you know your data, and have specs detailing it so you will need to identify and handle any ranges within that data:

data have;
  birthdate="Jan1975"; output;
  birthdate="1977"; output;
  birthdate="021978"; output;
run;

data want;
  set have;
  select(length(strip(upcase(birthdate))));
    when (9) new_bdate=input(birthdate,date9.);
    when (7) new_bdate=input(cats("11",birthdate),date9.);
    when (4) new_bdate=input(cats("11JUL",birthdate),date9.);
when (6) new_bdate=input(cats(substr(birthdate,1,2),"JUL",substr(birthdate,3,4)),date9.); otherwise; end; format new_bdate date9.; run;
Super Contributor
Posts: 490

Re: date impute

data Dte;
input birthdate $10.;
cards;
Jan1975
1977
021978
run;
data want ;
set Dte;
format date date9.;
if length(birthdate)=4 then date=input('11JUL'!!trim(birthdate),date9.);
else if length(birthdate)=6 then date=
input(substr(birthdate,1,2)!!'JUL'!!substr(birthdate,3,4),date9.); 
else if length(birthdate)=7 then date=input('11'!!trim(birthdate),date9.); 
run;
Super User
Posts: 9,687

Re: date impute

There are many rules you need consider about.

 

data Dte;
input birthdate $10.;
cards;
Jan1975
1977
021978
;
run;
data want;
 set Dte;
 if prxmatch('/\d\d\w\w\w\d\d\d\d/',birthdate ) then new=input(birthdate,date9.);
  else if prxmatch('/\w\w\w\d\d\d\d/',birthdate ) then new=input('11'||birthdate,date9.);
   else if prxmatch('/\d\d\d\d\d\d/',birthdate ) then new=input(substr(birthdate,1,2)||'Jul'||substr(birthdate,3),date9.);
    else if prxmatch('/\d\d\d\d/',birthdate ) then new=input('11Jul'||birthdate,date9.);
format new date9.;
run;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 727 views
  • 0 likes
  • 4 in conversation