topic Re: Date Imputation in SAS Programming

Date Imputation

honeyblue — Wed, 29 Jul 2020 03:38:12 GMT

data date;
input sdate $10.;
datalines;
1990
5 12 2001
10 10 2010
8 2018
1990
20 1 2017
12 2016

5 2017
15 12 2017
;
run;

I want 2 variables which are in bold with the below conditons

New date= variable

if day is missing then set to 15
if day and month missing then set to january 01
if day,month & year are missing then set to today's date

Abbreviation = variable

if day is missing then set to D
if day and month missing then set to M
if day,month & year are missing then set to Y
Create brthdtc and dtcflag variables by using day, month & year variables.

Re: Date Imputation

Tom — Wed, 29 Jul 2020 03:47:11 GMT

Count the number of "words" and then adjust from there.

data want;
  set have;
  dtcflag = char('YMD ',countw(sdate)+1);
  select (dtcflag);
    when (' ') brthdtc = input(sdate,ddmmyy10.);
    when ('D') brthdtc = input('15 '||sdate,ddmmyy10.);
    when ('M') brthdtc = input('01 01 '||sdate,ddmmyy10.);
    otherwise brthdtc=date();
  end;
  format brthdtc date9.;
run;

Obs    sdate         dtcflag      brthdtc

  1    1990             M       01JAN1990
  2    5 12 2001                05DEC2001
  3    10 10 2010               10OCT2010
  4    8 2018           D       15AUG2018
  5    1990             M       01JAN1990
  6    20 1 2017                20JAN2017
  7    12 2016          D       15DEC2016
  8                     Y       28JUL2020
  9    5 2017           D       15MAY2017
 10    15 12 2017               15DEC2017

Re: Date Imputation

honeyblue — Thu, 30 Jul 2020 02:59:56 GMT

Can you please explain in details step by step

Thank You

Re: Date Imputation

Tom — Thu, 30 Jul 2020 03:24:28 GMT

data want;
  set have;
  dtcflag = char('YMD ',countw(sdate)+1);
  select (dtcflag);
    when (' ') brthdtc = input(sdate,ddmmyy10.);
    when ('D') brthdtc = input('15 '||sdate,ddmmyy10.);
    when ('M') brthdtc = input('01 01 '||sdate,ddmmyy10.);
    otherwise brthdtc=date();
  end;
  format brthdtc date9.;
run;

The COUNTW() function counts the number of words. So you should get 0,1,2 or 3 words from your string. The CHAR() function returns the character in the position specified. Positions count from 1 so you need to add one to the number of words in case there are no words in the string. So when there are no words you take the first character, Y.

The SELECT statement is a multi way branching tools. So based on whether the flag is one of the four possible value a different statement is called.

When you have no words use today's date.

When you have one word prefix the string with 01 01 and convert it to a date.

When you have two words prefix the string with 15 and convert it to a data.

When you have three words just convert the string as it is to a date.

The DDMMYY informat reads strings in DMY order and converts them into date values.

The FORMAT statement attaches the DATE format to the variable so the number of days stored in it will print so that humans can understand it.

Re: Date Imputation

honeyblue — Thu, 30 Jul 2020 21:06:43 GMT

thank you