Hi!
I have a data set that contains patient id numbers and 22 drug id value columns (DrugID1 - DrugID22) for a large patient population. I'm trying to create a new variable for the dataset that indicates if and how often a specific id number "40" is observed for each patient.
I want my code to run so that SAS scans all of the drug id numbers for each patient and outputs the following in a new column:
0 - if the id value "40" was not identified in any of the patient's 22 drug id numbers
1 - if the id value "40" was identified in exactly one of the patient's 22 drug id numbers
2 - if the id value "40" was identified in more than one of the patient's 22 drug id numbers.
Thank you!
Something like
data want;
set have;
array drugs {*} drugid1-drugid22;
flag = 0;
do i = 1 to dim(drugs);
flag + (index(drugs{i},'40') > 0);
end;
if flag > 2 then flag = 2;
drop i;
run;
The easiest way would be create an array to hold all of your drug IDs and then loop through and count how many are equal to the specific drug ID you're looking for.
data temp;
input ID DrugID1 DrugID2;
datalines;
1 40 57
2 40 40
;
run;
data temp2;
set temp;
by id;
if first.id then count=0;
array drugs {*} DrugID:;
do i = 1 to dim(drugs);
count + (drugs[i]=40);
end;
run;
I created dataset temp just to have some data to illustrate my point. The input statement tells SAS what to read in from the data lines I will be providing and the datalines statement start off the listing of the raw data.
Your code would have only one DATA step and should look similar to this:
data Final_drug_counts;
set Final_drug;
by seqn;
if first.seqn then count=0;
array drugs {*} DrugID:;
do i = 1 to dim(drugs);
count + (drugs[i]=40);
end;
run;
Your other variables will remain in the dataset, unchanged.
Something like
data want;
set have;
array drugs {*} drugid1-drugid22;
flag = 0;
do i = 1 to dim(drugs);
flag + (index(drugs{i},'40') > 0);
end;
if flag > 2 then flag = 2;
drop i;
run;
That worked!! Thank you!!
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.