How do I select the last valid column from a data set

[terjeph]

I hava dataset indicating where people lived the last years of life. I'd like to select the last variable (the zip code during the death year) for each observation (patientID) and write it to a new file. Please observe patientID 4 which have some missings at the first years.

Data have:

PatientID zip2008 zip2009 zip2010 zip2011 zip2012

1                         304         304         304

2                        403

3                        506         506          704

4                                                          809       809      809

data want:

PatientID   LastZip

1                    304

2                   403

3                   704

4                   809

Anyone?

[quickbluefish]

data want;
set have;
array z {*} zip:;
length lastZip $5;
do i=1 to dim(z);
    if not missing(z[i]) then lastZip=z[i];
end;
keep patientID lastZip;
run;

Change the length statement as appropriate -- this is currently assuming zip is a character variable of length 5 ($5).  

[PaigeMiller]

Better to arrange your data in the long format.

data have;
input patient_id year zip;
cards;
1 2008 304
1 2009 304
1 2010 304
2 2008 403
3 2008 506
3 2009 506
3 2010 704
4 2010 809
4 2011 809
4 2012 809
;

data want;
    set have;
    by patient_id;
    if last.patient_id;
run;

[Tom · Accepted solution]

COALESCE() or COALESCEC() if the variables are character should do it.

Just use a descending order variable list.

data want;
  set have;
   lastzip=coalescec(of zip2012-zip2008);
run;

[quickbluefish]

This is interesting - I had no idea you could specify a variable list in descending order.

[terjeph]

Simple and elegant!