BookmarkSubscribeRSS Feed
Calcite | Level 5



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;




data w.drug;

set w.drug;


if Type="AB" then Drug_Group=1;

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



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.


Super User

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. 



Super User

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;

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

    *Set row counter for each person to 0;

*Increment to identify row for each person;

*Place drug in correct array variable;

*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; 


Rhodochrosite | Level 12

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;
   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;
   call missing(of k[*]);
Super User

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"

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4 in conversation