Assigning drug groups

[sasworker16]

Hello,

I need a very urgent help.

My data set looks like the following, basically only two variables: Person ID and Drug_Type, arranaged like this

-------------------------------------------------------

Person_ID  Drug_Type  Prescription_Date

10001               A                  2015-01-01

10001               B                  2015-03-05

10001               B                  2015-05-10

10002               C                  2015-02-01

10002               C                  2015-07-05 

10002               D                  2015-09-15

 -------------------------------------------------------

I need to group them as the following

#1) summarize the First, Second, and Third Drug that the person took, 

#2) if a person took drug  A & B=group 1, if a person took drug C & D=group2, so what I want looks like the following:

--------------------------------------------------------------------------------------------------------------------------------------------------

Person ID    Drug_Group     First_Prescription_Date              First_Drug         Second_Drug        Third_Drug   

10001                    1                2015-01-01                                    A                         B                           B

10002                    2                2015-02-01                                    C                         C                           D

--------------------------------------------------------------------------------------------------------------------------------------------------

here is what I tried:

data w.drug; 

set w.drug;

Type=First_Drug|Second_Drug|Third_Drug;

run;

data w.drug;

set w.drug;

Type=drug_type;

if Type="AB" then Drug_Group=1;

else if Type="CD" then Drug_Group=2;

run;

I am lost here....and cannot think of a way to assign the first prescription day as well...

I am in a very urgent situation. Any help or advice would be very much appreciated. 

Thank you.

[Reeza]

Will they always only have three drugs?

You need to transpose your data - in this case a data step is ideal. 

You can use BY group processing to identify the first date.

If you need urgent help, please post your data as a data step in the future, I hate writing input code. 

[Reeza]

Untested - and assumes your data is only 3 drugs and it's sorted by patient.

data want;
set have;
by person_id;

/*Hold values across rows*/
retain first_prescription_date drug1 drug2 drug3;

/*Set up array for easier referencing*/
array drug(3) $ drug1-drug3;

if first.person_id then do;
  *store first prescription date across rows;
   first_prescription_date=prescription_date;

   *set drugs to missing so data from previous rows dont spill over;
   call missing(of drug(*)); 

    *Set row counter for each person to 0;
   count=0;
end;

*Increment to identify row for each person;
count+1;

*Place drug in correct array variable;
drug(count)=drug_type;

*if on last record per person, check for drug_group. I use WHICHC to check because the order could vary, ie AB is not the same as BA and checking for all possible combinations is cumbersome, so check for each individual occurence;

if last.person_id then do;
    if whichc('A', of drug(*))>0 and whichc('B', of drug(*))>0 then drug_group=1;
    else if whichc('C', of drug(*))>0 and whichc('D', of drug(*))>0 then drug_group=2; 
end;

run;

[KachiM]

Another Version.

data want(drop = i Drug_Type);
array k[3] $1 _temporary_ ;
array drug[3] $1 First_D Second_D Third_D;
   do i = 1 by 1 until(last.Person_ID);
      set have;
      by Person_ID;
      drug[i] = Drug_Type;
      k[i] = Drug_Type;
   end;
   do i = 1 to dim(k);
      if ( k[i] = 'A' | k[i] = 'B') then drug_group = 1;
      else if ( k[i] = 'C' | k[i] = 'D') then drug_group = 2;
   end;
   call missing(of k[*]);
run;

[ballardw]

What would expect for a result if you have data like?

Person_ID  Drug_Type  Prescription_Date

10001               A                  2015-01-01

10001               B                  2015-03-05

10001               D                  2015-05-10

I am not clear on what might be meant by "summarize the First, Second, and Third Drug that the person took"