topic create subset of data based on variables in SAS Programming

create subset of data based on variables

yoyong555 — Thu, 25 Jul 2019 03:24:13 GMT

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

Re: create subset of data based on variables

ScottBass — Thu, 25 Jul 2019 03:36:13 GMT

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;

Re: create subset of data based on variables

yoyong555 — Thu, 25 Jul 2019 03:47:22 GMT

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.

Re: create subset of data based on variables

Kurt_Bremser — Thu, 25 Jul 2019 05:37:48 GMT

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