Scanning multiple variables to create new variable

[erich20]

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!

[antonbcristina]

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;

[erich20]

Thank you for the quick response!

I haven't create arrays before so I have a couple questions....

The data set I'm working with also contains a lot of other variables that I
want to keep (e.g. bmi, age, sex, etc). When creating the temp dataset as
seen in your code can I just set my dataset (which is titled "final_drug")
instead of using the input function?

If yes, then do I still include the datalines function?

Thank you

*More information:*

Name of data set : Final_drug;
Name of patient id variable: seqn;
Name of drug id variables: drugID1, drugID2, drugID3,..., drugID22

[antonbcristina]

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.

[Kurt_Bremser · Accepted solution]

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; 

[erich20]

That worked!! Thank you!!