topic How do I select the last valid column from a data set in SAS Programming

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

terjeph — Thu, 23 Jan 2025 15:56:56 GMT

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?

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

quickbluefish — Thu, 23 Jan 2025 16:32:43 GMT

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).

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

PaigeMiller — Thu, 23 Jan 2025 16:47:03 GMT

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;

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

Tom — Thu, 23 Jan 2025 16:49:49 GMT

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;

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

quickbluefish — Thu, 23 Jan 2025 17:02:11 GMT

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

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

terjeph — Thu, 23 Jan 2025 17:10:27 GMT

Simple and elegant!