07-21-2017 09:59 PM
I have a dataset of patients, where each patient can have one or many admissions and one or many surgeries in each admission:
I want to choose the first surgery, and if had many admissions to choose the first surgery in last admission.
How can I do it?
I use version 9.4
07-21-2017 10:14 PM
Well, you haven't told us some key information ... what are the names of the variables, are they SAS dates or something else, are there other observations in the data set that do not represent surgeries ... so here's a guess that is based on inventing some of the key information:
proc sort data=have;
by patient descending admission surgery_date;
07-21-2017 10:30 PM
Let's assume data set HAVE is sorted by PATIENT and ADMISSION_DATE. Each PATIENT/ADMISSON_DATE combination can have multiple records, some of which have variable TYPE="surgery". Then a double-do-until-last.by_var technique can be used:
data want; do recnum=1 by 1 until (last.patient); set have; by patient admission_date; where type='surgery'; if first.admission_date then wantrec=recnum; end; do recnum=1 by 1 until (last.patient); set have; by patient; where type='surgery'; if recnum=wantrec then output; end; run;
Both DO UNTIL loops read and count only "surgery" records. The first DO UNTIL keeps updating WANTNUM so that it is set to the earliest surgery-record number ("if first.admission_date") within an admission date. Of course the last time WANTNUM is updated is for the most recent admission_date. The second DO UNTIL re-reads the same records and outputs only the one matching WANTNUM.
Note the "if first.admission_date" says to test if the record-in-hand is the start of a new admission_date. But we know it is actually the first "surgery" record within the date (not neccessarily the first overall record within date), because of the filtering of the where type='surgery' statement