I have an SPSS file with each patient being recorded multiple times (1-8 times per patient). Each record is a different row in SPSS (I have over 4000 records). Now, I would like to select the latest year of inclusion of each patient. However, I cannot figure out how to do that. So for example, I would like to select the year 2019 of patient 1 (since 2019 is the last year the patient participated)and the year 2009 of patient 2.

1 2017 1 2018 1 2019 2 2008 2 2009

I hope someone can help me with how to do it. So far, I've tried a lot of options but I cannot find an answer. Thanks for helping me out. I'm using IBM SPSS Statistics version 23.



1 Answer 1


You can use AGGREGATE by patient id to create a new variable that computes the last year. Then select only cases with the same year.

First build a random dataset to check the method.

set rng=mc seed=17760704.
new file.
input program.
    numeric id (f5) year (f4) some_data (f6.3).
    loop #i=1 to 200.
        compute #n=trunc(rv.uniform(1,10)).
        loop #j=1 to #n.
            compute year=trunc(rv.uniform(2000,2010)).
            compute some_data=rv.normal(0,1).
            compute id=#i.
            end case.
        end loop.
    end loop.
    end file.
end input program.

Two ways to solve the problem with AGGREGATE: either compute the maximum year in each group, or presort by id/year and get the last year in each id group.

aggregate /outfile=* mode=addvariables /break=id /maxyear=max(year).
select if year=maxyear.

Second method:

sort cases id year.
aggregate /outfile=* mode=addvariables /presorted /break=id /maxyear=last(year).
select if year=maxyear.

Note that if a patient may have several records with the same year, then the commands above will keep all cases matching the last year.

  • $\begingroup$ Wow thanks for your response! I will try that today :). Do to think it will work as well when the last year of inclusion differs? Some of my patients participates untill 2018 and some dropped out earlier. $\endgroup$
    – Samm__
    Commented Aug 7, 2020 at 7:35
  • $\begingroup$ @Samm__ Yes, it will select the last year for each patient. $\endgroup$
    – user291827
    Commented Aug 7, 2020 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.