topic Re: Scanning multiple variables to create new variable in SAS Programming

Scanning multiple variables to create new variable

erich20 — Thu, 26 Apr 2018 16:46:54 GMT

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!

Re: Scanning multiple variables to create new variable

antonbcristina — Thu, 26 Apr 2018 16:55:06 GMT

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;

Re: Scanning multiple variables to create new variable

Kurt_Bremser — Thu, 26 Apr 2018 16:56:38 GMT

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;

Re: Scanning multiple variables to create new variable

erich20 — Thu, 26 Apr 2018 17:13:18 GMT

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

Re: Scanning multiple variables to create new variable

erich20 — Thu, 26 Apr 2018 17:20:54 GMT

That worked!! Thank you!!

Re: Scanning multiple variables to create new variable

antonbcristina — Thu, 26 Apr 2018 17:22:43 GMT

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.