topic Re: date impute in SAS Programming

date impute

RTelang — Wed, 24 Feb 2016 15:33:37 GMT

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

Jan1975

1977

19JUN1977

021978

HOW can i modify this code to get required output.

Re: date impute

RW9 — Wed, 24 Feb 2016 09:06:37 GMT

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.

Re: date impute

RTelang — Wed, 24 Feb 2016 09:08:17 GMT

it represents ddyyyy with month missing

Re: date impute

mohamed_zaki — Wed, 24 Feb 2016 09:10:48 GMT

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;

Re: date impute

RW9 — Wed, 24 Feb 2016 09:17:29 GMT

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&colon;

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;

Re: date impute

Ksharp — Wed, 24 Feb 2016 09:23:20 GMT

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;