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 |
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;
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;
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.