Flag distinct rows within a by variable using vertical data

[sk123]

Hello,

I want to create a variable that identifies if a specific patient receives more than one dosage type of a given medication. So if the patient only receives one dosage type than the new variable would be 1 for all given rows for that patient. If a patient receives two different dosage types than the new variable would be 1 for the first dosage type and 2 for the 2nd dosage type. Below is an example of the data and the variable I want to create. (I'm using SAS 9.2).

Thank you in advance!

ID	Medication_dose	Variable_wanted
1	500mg	1
1	500mg	1
1	500mg	1
1	100mg	2
1	100mg	2
2	250mg	1
2	250mg	1
2	250mg	1
3	500mg	1
3	500mg	1
3	250mg	2
3	100mg	3

[Kurt_Bremser]

Sort by id and medication_dose.

In a data step, use

by id medication_dose;
retain variable_wanted;
if first.id then variable_wanted = 0;
if first.medication_dose then variable_wanted + 1;

[PeterClemmensen · Accepted solution]

data have;
input ID Medication_dose $;
datalines;
1 500mg
1 500mg
1 500mg
1 100mg
1 100mg
2 250mg
2 250mg
2 250mg
3 500mg
3 500mg
3 250mg
3 100mg
;

proc sort data=have;
   by ID Medication_dose ;
run;

data want;
   set have;
   by ID Medication_dose;
   if first.ID then variable_wanted=0;
   if first.Medication_dose then variable_wanted+1;
run;

[sk123]

Thank you! This worked perfectly.

[PeterClemmensen]

Or if you want to avoid sorting. 

Also, welcome to the SAS Communities 🙂

data have;
input ID Medication_dose $;
datalines;
1 500mg
1 500mg
1 500mg
1 100mg
1 100mg
2 250mg
2 250mg
2 250mg
3 500mg
3 500mg
3 250mg
3 100mg
;

data want;
   if _N_ = 1 then do;
      declare hash h();
      h.defineKey('ID', 'Medication_dose');
      h.defineDone();
   end;

   do until (last.ID);
      set have;
      by ID notsorted;
      if h.check() ne 0 then do;
         Variable_wanted+1;
         h.add();
      end;
      output;
   end;
   Variable_wanted=0;
run;

[sk123]

Thank you!