Scanning multiple variables to create new variable

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Scanning multiple variables to create new variable

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!

 


Accepted Solutions
Solution
‎04-26-2018 01:20 PM
Super User
Posts: 10,258

Re: Scanning multiple variables to create new variable

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; 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
SAS Employee
Posts: 24

Re: Scanning multiple variables to create new variable

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;
New Contributor
Posts: 3

Re: Scanning multiple variables to create new variable

Posted in reply to antonbcristina
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

SAS Employee
Posts: 24

Re: Scanning multiple variables to create new variable

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.

Solution
‎04-26-2018 01:20 PM
Super User
Posts: 10,258

Re: Scanning multiple variables to create new variable

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; 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Scanning multiple variables to create new variable

Posted in reply to KurtBremser

That worked!! Thank you!!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 136 views
  • 1 like
  • 3 in conversation