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 |
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.