filling missing values

[gnrslasher37]

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.

[RW9]

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.

[Astounding]

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;

[gnrslasher37]

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.

[Astounding]

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.

[Haikuo]

@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;

[Astounding]

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!

[Ksharp]

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;