ID 2 has a sequence of 2, 3, 4. So why does it have start level 3 and end level 2?
Assuming that is an error, then here's a technique to pass through each id twice. The first pass determines the start and end levels. the second pass re-reads the id and outputs each obs with the recently determined start and end levels:
data want;
set have (in=firstpass) have (in=secondpass);
by id;
retain start_level end_level;
if first.id then start_level=level;
if in=firstpass then end_level=level;
if secondpass;
run;
The by statement create the dummy variables first.id and last.id, providing a way to know when an ID is beginning or ending. So when FIRST.ID is true, then you have start_level.
Since the HAVE dataset is specified twice in the SET statement, the presence of the BY ID statement causes the data to be interleaved - i.e. each id is completely read twice before progressing to the next id.
The retain tells SAS not to reset the variables to missing.
And the "if in=firstpass" keeps update the end_level as long as your are on the first pass.
Finally, there is a subsetting if: "if secondpass;"
The firstpass dummy is tested
... View more