topic end date imputation in SAS Programming

end date imputation

112211 — Sun, 06 Aug 2023 05:10:19 GMT

Hi Team,

I need help in programming part for date imputation. Here is the raw data below:

Raw data:

data vy;
input id date $ 10.;
cards;
101 21aug2020
102 ukfeb2016
103 ukaug2019
104 ukunk2020
105 07aug2018
106 ukdec2020
107 ununkunkk
108 ukfeb2019
;

I need to do date imputation based on two points as below:

1. If both month and day are missing, then set to December 31.
2. If only day is missing, then set to last day of the month.

Re: end date imputation

Seadrago — Thu, 10 Aug 2023 19:56:47 GMT

data vy1;

set vy;

/*Seperate date into day, month, year */

dayc=substr(date,1,2);

monthc=substr(date,3,3);

yearc=substr(date,6,4);

if yearc ne "unkk" then do; /*One row has year missing, not able to impute */

/*both month and day missing*/

if dayc="uk" and monthc="unk" then do;

dayi="31";

monthi="DEC";

end;

/*only day missing*/

if dayc="uk" and monthc ne"unk" then do;

myrc=cats(monthc)||cats(yearc);

myn=input(myrc, anydtdte7.);

lastday=intnx('month',myn,0,'E');

end;

/*get imputed date*/

if dayi ne "" and monthi ne "" then

date_impc=cats(dayi)||cats(monthi)||cats(yearc);

if lastday ne . then date_impc=put(lastday, date9.);

format date_imp date9.;

date_imp=input(date_impc, date9.);

end;

run;

Re: end date imputation

Reeza — Thu, 10 Aug 2023 20:11:40 GMT

If month, day & year are missing( ID 107) does it follow Rule #1?

Re: end date imputation

Seadrago — Sat, 12 Aug 2023 02:49:08 GMT

I don’t believe so. ‘if yearc ne “unkk” then do;’ statement removes any year with missing from below logic

Re: end date imputation

mkeintz — Sat, 12 Aug 2023 13:04:38 GMT

All you really need to do is

If the first character is not 'u' then it's a normal date9. string
else if the third character (where the month begins) is not 'u' then you only need to impute the leading '01'
else if the sixth character (where the year begins) is a number, then impute the leading '01dec'
otherwise the date value is set to missing
 
data vy;
input id date $ 10.;
cards;
101 21aug2020
102 ukfeb2016
103 ukaug2019
104 ukunk2020
105 07aug2018
106 ukdec2020
107 ununkunkk
108 ukfeb2019
;
data want;
set vy;
format datvalue date9.;
if char(date,1) ^= 'u' then datvalue=input(date,date9.);
else if char(date,3)^='u' then datvalue=input('01'||substr(date,3,7),date9.);
else if '0'<=char(date,6)<='9' then datvalue=input('01dec'||substr(date,6),date9.);
run;
 
 

Re: end date imputation

Kurt_Bremser — Sat, 12 Aug 2023 06:57:21 GMT

I already posted an answer to this in the thread that has been deleted in the meantime:

data want;
set have (rename=(date=_date));
if substr(_date,6) = "unkk"
then date =.;
else do;
  if substr(_date,3,3) = "unk" then substr(_date,3,3) = "dec";
  if substr(_date,1,2) = "uk"
  then do;
    substr(_date,1,2) = "01";
    date = input(_date,date9.);
    date = intnx('month',date,0,'e');
  end;
  else date = input(_date,date9.);
end;
format date yymmdd10.; * always use the ISO format for clarity;
drop _date;
run;