topic Auto filling rows in a dataset in SAS Programming

Auto filling rows in a dataset

hwangnyc — Wed, 16 Jan 2019 19:01:42 GMT

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

Re: Auto filling rows in a dataset

novinosrin — Wed, 16 Jan 2019 19:06:50 GMT


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;

Re: Auto filling rows in a dataset

r_behata — Wed, 16 Jan 2019 20:55:35 GMT

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;

Re: Auto filling rows in a dataset

mkeintz — Wed, 16 Jan 2019 21:38:53 GMT

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.