There are some questions you need to consider about how complete your example might be.
The first is are all of your compounds exactly two compounds? If you have 3, 4 or more then you should say so.
Second, and possibly more critical, how consistent is the spelling? I have done a very small amount of working with drug names and found problems in that regard. Spelling includes case: "morphine" is not the same as "Morphine" for comparisons and need to know if code needs to adjust for case or not.
Third, are your compounds ALWAYS indicated by a /? If you have other characters then separating the individual compounds requires knowledge of that use.
This works for the first rows of your example. Note the first data step is to have something to code with. HINT HINT HINT.
data have;
infile datalines truncover;
informat id $5. drug1-drug7 $40.;
input id drug1 drug2 drug3 drug4 drug5 drug6 drug7;
datalines;
001 diazepam/nordiazepam diazepam morphine nordiazepam
002 sertraline/norsertraline methadone norsertraline pseudoephedrine sertraline
003 caffeine lidocaine opiates
004 thc/carboxy-thc citalopram/desmethylcitalopram caffeine carboxy-thc citalopram desmethylcitalopram thc
005 thc/carboxy-thc carboxy-thc thc
006 caffeine carboxy-thc phenethylamine
;
data want;
set have;
array d (*) drug1-drug7;
array c (7);
/* helper array of indicators of COMPOUND values*/
do i=1 to dim(d);
c[i] = (findc(d[i],'/')>0);
end;
/* temp variables to hold pieces of the compound*/
length temp1 temp2 $ 40;
/* see if any of the array elements are compounds*/
comp= whichn(1,of c(*));
if comp>0 then do until (comp=0);
/*get the individual components of the compounds*/
temp1=scan(d[comp],1,'/');
temp2=scan(d[comp],2,'/');
do i=1 to dim(d);
if d[i]=temp1 then call missing(d[i]);
if d[i]=temp2 then call missing(d[i]);
end;
/* remove the indicator for the processed compound from the helper array*/
c[comp]=0;
/* see if there is another compound*/
comp= whichn(1,of c(*));
end;
/* remove temporary variables*/
drop i temp1 temp2 c1-c7 comp;
run;
Arrays are a basically tool when you need to do something similar to a bunch of variables. They are shorthand for addressing individual variables by using an array name and a numeric index for the position of the variable of the array.
This code uses a function WHICHN that searches a list of variables, in this case the array C, for presence of the numeric value 1 to know which are the compounds. We then find the first compound, get the pieces, then search through the drug array to find the pieces with the value and use Call missing to remove the value.
After the first compound is addressed we remove the index from the helper array and see if there is another. The Whichn, and the companion for character values Whichc, returns 0 when the target value is not found. So setting the C value to 0 removes it from the list.
Note: This does not do anything to move variables to the left to fill in the missing values.
That is a basic simple task with an array.
