Auto filling rows in a dataset

[hwangnyc]

Hi everyone,

This is what I have:

Period	Staff	ID
Start	John	1234
End		1234
End		1234
End		1234
Start	Dira	9876
End		9876

and this is what I want:

Period	Staff	ID
Start	John	1234
End	John	1234
End	John	1234
End	John	1234
Start	Dira	9876
End	Dira	9876

[novinosrin]

data have;
input Period $	Staff $	ID;
cards;
Start	John	1234
End	.	 	1234
End	. 	1234
End	 .	1234
Start	Dira	9876
End	 	. 9876
;
data want;

update have(obs=0) have;
by id;

output;

run;

[r_behata]

data have;
input Period $	Staff $ ID $;
infile cards dsd;
cards;
Start,John,1234
End,,1234
End,,1234
End,,1234
Start,Dira,9876
End,,9876
;
run;


data want;
	retain Staff_;
	set have;
	by id  ;
	if first.id then Staff_=Staff;
	else if 	missing(staff) then staff=staff_;

	drop Staff_;
run;

[mkeintz]

If the UPDATE statement offered by @novinosrin seems slightly opaque, there is another way to avoid using RETAIN as per @r_behata.  You can utilize the fact that any variable read by a SET is automatically retained until that variable is explicitly overwritten.  So below the STAFF variable is only read in when period='Start', and therefore will be retained until the next period='Start' by executing the SET statement conditionally (i.e. "IF period='Start' then set ...").

data have;
input Period $	Staff $ ID $;
infile cards dsd;
cards;
Start,John,1234
End,,1234
End,,1234
End,,1234
Start,Dira,9876
End,,9876
;
run;
data want;
  set have (drop=staff);
  if period='Start' then set have (keep=staff) point=_n_;
run;

But remember one thing - while variable STAFF is the only variable read in by the conditional SET statement, it is also dropped from the unconditional SET.

Now if you want to propagate ALL variables forward to replace missing values across an ID, use @novinosrin's UPDATE suggestion.  But if you only wish to do it for a particular subset of variables, try the above.