topic Re: filling missing values in SAS Studio

filling missing values

gnrslasher37 — Mon, 01 Aug 2016 15:48:50 GMT

I have a dataset sorted by date and time with missing values.I have not been able to fill in those missingvalues by both end successfully, means only previous value has been succeeded.The set is as follows:

Date Time Price

. 20:12 1440

20030223 23:57 1200

. 23:59 1150

. 00:01 1100

20030224 02:34 1400

The output should be

Date Time Price

20030223 20:12 1440

20030223 23:57 1200

20030223 23:59 1150

20030224 00:01 1100

20030224 02:34 1400

Thanks.

Re: filling missing values

RW9 — Mon, 01 Aug 2016 16:18:41 GMT

The problem I see it is that you can't say for certain that th missings are any date at all, the first row could be 20:12 on 22feb2016, or 21feb2016 or 24feb2016. There's no logical way of telling from that data what each row's date is, and that can only come really from the system which does know. Now its possible to do, however I don't recommend just guessing like this. Assign a day based on the data order, i.e. retain day starting at 0, then when time < previous time, assume its gone over 24:00, so add one to day. Then get the minimum date, and add day to it for each row. However not really a good logical approach.

Re: filling missing values

Astounding — Mon, 01 Aug 2016 17:38:39 GMT

Here's an approach ... but it works only under strict conditions. First, for each group of observations there must be exactly one observation with a nonmissing DATE. Second, the TIME values must be legitimate SAS time values, not a character string. Finally, the TIME value for the first observation on a new date must come before the last TIME value from the previous date. (For example using your sample data, 00:01 must be earlier than 23:59.)

data want;

set have;

timedif = dif(time);

if timedif < 0 then set have (keep=date where=(date > .) rename=(date=replacement_date));

date = replacement_date;

drop timedif;

run;

Re: filling missing values

Haikuo — Mon, 01 Aug 2016 17:57:20 GMT

@Astounding, I think you might have overlooked one tiny detail, that is for a dataset option, 'where' happens after 'rename', so it should be

if timedif < 0 then set have (keep=date where=(replacement_date > .) rename=(date=replacement_date));

@gnrslasher37, the following approach requires almost the same condition as the one provided by @Astounding, except you can have multiple populated 'date' value within the same day, and the only thing to determine if a new date starts is the current 'time' less than or equal to the last 'time'.

data have;
	input Date                Time :time5.           Price;
	format time time5.;
	cards;
.                      20:12          1440
20030223       23:57          1200
.                      23:59          1150
.                      00:01          1100
20030224       02:34          1400
;

data want;
	do _n_=1 by 1 until (time >= _t);
		set have;
		set have(firstobs=2 rename=(time=_t)) have(obs=1 drop=_all_);

		if not missing(date) then
			_d=date;
	end;

	do _n=1 to _n_;
		set have;
		date=_d;
		output;
	end;

	drop _:;
run;

Re: filling missing values

Astounding — Mon, 01 Aug 2016 18:00:35 GMT

That's absolutely right, my SET statement should read:

if timedif < 0 then set have (keep=date where=(replacement_date > .) rename=(date=replacement_date));

Good catch. Note to self: check your code!

Re: filling missing values

Ksharp — Tue, 02 Aug 2016 04:35:49 GMT

What if there are some missing DATE at the end of table , what you gonna do ?

data have;
	input Date                Time :time5.           Price;
	format time time5.;
	cards;
.                      20:12          1440
20030223       23:57          1200
.                      23:59          1150
.                      00:01          1100
20030224       02:34          1400
.       02:34          1400
.       02:34          1400
;
run;

data want;
retain temp1 temp2;
 do until(not missing(date) or last1);
  set have end=last1;
 end;
 if not missing(date) then temp1=date;

n+1;

 do until(not missing(date) or last2);
  set have end=last2;
  if n=1 then new_date=ifn(missing(date),temp1,date);
   else new_date=ifn(missing(date),temp2,date);
  output;
 end;
 if not missing(date) then temp2=date;

drop temp1 temp2 n; 
run;

Re: filling missing values

gnrslasher37 — Tue, 02 Aug 2016 06:44:25 GMT

The dataset has multiple timestamp data within a 24 hour circle of the day, thus the time interval will be looping in same way for each different day. So the time constraints you have mentioned cannot be hold up.

Re: filling missing values

Astounding — Tue, 02 Aug 2016 17:32:16 GMT

Actually, that's a perfect situation. My timestamp requirements could be restated this way.

Whenever a timestamp is earlier than the previous timestamp, that signals that the day should change. In fact, that's the only reliable signal that the day should change.