create subset of data based on variables

[yoyong555]

Hi everyone.

I need help.

Here is my sample data set.

type = procedure type (A or B)

data hosp1;
input Admission_Date date9. patient_name :$20.  gender :$1. weight type :$1;
cards;
11Nov2011 Jim M  100 B
03Feb2010 Luna  F  92  A
21Apr2011 Luna  F  88  A
04May2016 Grey  M  80  A
03May2010 Maria F  .   A
25Dec2012 Maria F  75  B
12Mar2013 Maria F  90  A
;
run;

I would like an output where if a patient had multiple admission dates (e.g. Maria), the information related to the earliest admission date is selected and if the patient had undergone both types of procedures (A and B) then the patient had Type B procedure.

So the output would look like this.

Name of patient         Gender          Weight     Type of procedure

Jim                                 M                    100         B

Luna                               F                     92          A

Grey                               M                    90          A

Maria                              F                     75          B

Thank you very much.

Yoyong

[ScottBass]

Fix your initial data step so I can paste it into SAS.  Using the Insert SAS Code icon. 

You have a typo in your target output (Grey weight=80 not 90)

if a patient had multiple admission dates (e.g. Maria), the information related to the earliest admission date is selected

Maria's earliest admission date is 03May2010, her weight is missing, not 75.

See if this helps:

data have ;
input admissiondate date9. patientname :$20. gender :$1. weight type :$1.;
format admissiondate date9.;
cards ;
11Nov2011 Jim M 100 B
03Feb2010 Luna F 92 A
21Apr2011 Luna F 88 A
04May2016 Grey  M  80  A
03May2010 Maria F . A
25Dec2012 Maria F 75 B
12Mar2013 Maria F 90 A
run ;

proc sql;
   create table want as
   select b.admissiondate, a.patientname, a.gender, a.weight, b.type
   from have a
   join (
   select patientname, 
          max(type) as type, 
          min(admissiondate) as admissiondate format=date9.
   from have
   group by patientname
   ) b
   on a.patientname=b.patientname
   and a.admissiondate=b.admissiondate
   ;
quit;

[yoyong555]

Thank you very much @ScottBass. Sorry about the typo (Grey weight = 80). Also, I wasn't very clear with the last part about the "earliest" admission date. What I actually meant was, if a patient has multiple admission dates and the information related to the earliest date is missing, the information related to the next date is selected. Hence, weight for Maria should be 75. Again, thank you.

[Kurt_Bremser]

Data step solution:

data have;
input admission_date date9. patient_name :$20.  gender :$1. weight type :$1.;
format admission_date yymmddd10.;
cards;
11Nov2011 Jim M 100 B
03Feb2010 Luna F 92 A
21Apr2011 Luna F 88 A
04May2016 Grey M 80 A
03May2010 Maria F . A
25Dec2012 Maria F 75 B
12Mar2013 Maria F 90 A
;

data want;
set have (rename=(gender=_gender weight=_weight type=_type));
by patient_name notsorted;
retain gender weight type;
if first.patient_name
then do;
  gender = _gender;
  weight = _weight;
  type = _type;
end;
else do;
  gender = coalescec(gender,_gender);
  weight = coalesce(weight,_weight);
  if _type > type then type = _type;
end;
if last.patient_name;
drop _: admission_date;
run;

proc print data=want noobs;
run;

Result:

patient_
  name      gender    weight    type

 Jim          M         100      B  
 Luna         F          92      A  
 Grey         M          80      A  
 Maria        F          75      B