Selecting second observation within multiple observations

[bourdeax]

Hello Everyone

Here is an example of the data that I am working with:

student_id         school                  enrollment_begin
1111111           University A            10JUN2015
1111111           University B            15AUG2015
2222222         University A            15AUG2014
2222222         University A            07JAN2015
2222222         University B            10JUN2015

I want to create a table to show the second observation (i.e.,university) for each person. So, with the data above, I would have a table that would end up looking like this:

student_id         school                  enrollment_begin
1111111           University B           15AUG2015
2222222         University A            07JAN2015

Any/all help is welcome. Thanks!

[ballardw]

Something like this should work if the data is grouped as you show:

data want;
   set have;
   by notsorted student_id;
   retain reccount;
   if first.student_id then reccount=1;
   else reccount= recount+1;
   if reccount=2;
   drop reccount;
run;

[bourdeax]

Thank you for the response. So should this code create a new variable called "reccount" which will contain a count of each persons entry? The code ran successfully, but I am not sure what it did to my data. Sorry, I am still fairly new to SAS/programming language altogether.

[ballardw]

The BY statement creates automatic variables for the first and last of each group of values. Those values are referenced as FIRST.variable name or LAST.variablename and are numeric values that have a value of 1 when true and 0 when false.

The code looks at each record and tests if the variable on the BY statement is actually first or not. The variable recount does count records, the RETAIN statement keeps the values from on iteration of the data step to the next. The count is set to 1 when the first record of each group is encountered, otherwise it is incremented.

If reccount=2;

is a subsetting IF. That means that only records that the IF is true for are kept.

Nothing should have been done to your data unless you used the same data set name on both the DATA and SET statements. Which, while syntactically legal and occasionally useful, is often a very bad idea as it completely rewrites the data set. If you make a logic error or have incorrect code the output can destroy your input and you will need to go back and recover.

It is a good idea to look up any code that looks new in the help files before asking additional questions. You may find other bits related to your needs that my answer might not include.

BTW, do you have any IDS that do not have 2 or more records? If so, they will get dropped as you specifically asked for the second records.

[monkeyboi]

I too got an error, then I realized 'reccount' was misspelled as 'recount'. Fixing this, fixed the error.

[Kamal5522]

data want;
set have;
by student_id;
if first.student_id then count=1;
else count+1;
if last.student_id;
drop count;
run;

This is the shortest and easy method

[Kamal5522]

small amendment in above code use if count=2 instead of if last.student_id

[Kamal5522]

in the above code we do no need to use retain statement

[ballardw]

---

@Kamal5522 wrote:
in the above code we do no need to use retain statement

---

The structure in the code of

Else count+1;

Implies a retain. Which makes the different code a style choice.

I am not really fond of certain not-quite-obvious behaviors. If the code for a data step is long you may not realize that the implied retain is present especially  when using inherited code or code that you have not revisited for awhile. Then make a small code change and spend time wondering why this variable is misbehaving in some form.