topic Date Imputation in SAS Programming

Date Imputation

mounikag — Thu, 01 Apr 2021 00:39:31 GMT

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.

Re: Date Imputation

PGStats — Thu, 01 Apr 2021 02:11:03 GMT

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;

Re: Date Imputation

qoit — Thu, 01 Apr 2021 02:13:19 GMT

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;

Re: Date Imputation

mounikag — Fri, 02 Apr 2021 18:33:49 GMT

Thank you so much. solution worked

Re: Date Imputation

mounikag — Fri, 02 Apr 2021 18:34:50 GMT

Thank you so much. solution worked