Write and run SAS programs in your web browser

filling missing values

Reply
Occasional Contributor
Posts: 12

filling missing values

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.

Super User
Super User
Posts: 7,668

Re: filling missing values

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.

Super User
Posts: 5,351

Re: filling missing values

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;

Occasional Contributor
Posts: 12

Re: filling missing values

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.

Super User
Posts: 5,351

Re: filling missing values

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.

Respected Advisor
Posts: 3,156

Re: filling missing values

[ Edited ]

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

 

Super User
Posts: 5,351

Re: filling missing values

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!

Super User
Posts: 9,854

Re: filling missing values

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;

Ask a Question
Discussion stats
  • 7 replies
  • 362 views
  • 0 likes
  • 5 in conversation