topic Re: end date imputation in SAS Programming

end date imputation

190119 — Sun, 06 Aug 2023 06:16:53 GMT

Hi Team,

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.

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

Re: end date imputation

Kurt_Bremser — Sun, 06 Aug 2023 07:09:14 GMT

data want;
set have (rename=(date=_date));
if upcase(substr(_date,6)) = "UNKK"
then date = .;
else do;
  if upcase(substr(_date,3,3)) = "UNK"
  then substr(_date,3,3) = "DEC";
  if upcase(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.;
drop _date;
run;

Untested, posted from my tablet.

Re: end date imputation

Kurt_Bremser — Sun, 06 Aug 2023 07:14:31 GMT

Please do not use attachments for code. Copy/paste the code into a window opened with the "little running man" button. That way we can immediately see and use the code (e.g. copy/paste into SAS Studio) without having to download first.

Re: end date imputation

ballardw — Sun, 06 Aug 2023 15:19:41 GMT

Here's the attached code:

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

The real question in my mind is the un unk and unkk. Where did they come from? Someone made them. Perhaps address that so that you get values like

101 21aug2020
102 feb2016
103 aug2019
104 2020
105 07aug2018
106 dec2020
107 .
108 feb2019
;

Testing the length of the variable would then allow selecting the proper handling...

Re: end date imputation

mkeintz — Sun, 06 Aug 2023 16:37:47 GMT

You can:

data want;
  set have;
  if date='ununkunkk'   then sasdate=.;
  else if date=:'ukunk' then sasdate=input(cats('31dec',substr(date,6,4)),date9.);
  else if date=:'uk'    then sasdate=intnx('month',input(substr(date,3,7),monyy7.),0,'end');
  else sasdate=input(date,date9.);
  format sasdate date9.;
run;

You did provide your sample data inside a working data step - thanks. BUT ...

Please make it viewable by using the insert code icon ("running man") rather than as an attachment.