Date Imputation

[mounikag]

Hi Team,

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

Raw data:

subj       date

101        2020-01-28      

102        2020-01

103        2020-02

104        2020-04

105        2020

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.

Output should be as below:

OUTPUT data:

subj       date

101        2020-01-28      

102        2020-01-31

103        2020-02-28

104        2020-04-30

105        2020-12-31

Could you please provide help in programming part. TIA.

[PGStats · Accepted solution]

Try this:

data have;
input subj d :$10.;
datalines;
101        2020-01-28      
102        2020-01
103        2020-02
104        2020-04
105        2020
;

data want;
set have;
year = input(scan(d, 1, "-"), ?? best.);
month = input(scan(d, 2, "-"), ?? best.);
day = input(scan(d, 3, "-"), ?? best.);
date = mdy(coalesce(month, 12), coalesce(day, 1), year);
if missing(day) then date = intnx("month", date, 0, "END");
format date yymmdd10.;
run;

proc print; run;

[mounikag]

Thank you so much. solution worked

[qoit]

See below:

data want (drop=date new_var1);
	input subj$ date $10.;
	format date_var new_var1 yymmdd10.;

	if length(compress(date,"-")) = 4 then
		date_var = input(cats(date,"-12","-31"),yymmdd10.);
	else if length(compress(date,"-")) = 6 then
		do;
			new_var1 = input(cats(date,"-01"),yymmdd10.);
			date_var = intnx('month',new_var1,0,'e');
		end;
	else
		do new_var1 = .;
			date_var = input(date,yymmdd10.);
		end;

	datalines;
101 2020-01-28
102 2020-01
103 2020-02
104 2020-04
105 2020
;
run;

[mounikag]

Thank you so much. solution worked