Extract values for two related variables

[Alireza_Boloori]

Hello everyone,

I have three variables, drug_ID, drug_name, and strength. Here is an example of the data format for these three variables:

drug_ID   drug_name    strength

1              A/B/C             5 mg-10 mg-5 mg

2              D                   10 mg

3              E/F                7 mg-15mg

4              E/B               13 mg-8 mg

where A, B, ... are active ingredients of a drug. As you can see, for each observation, each active ingredient has a specific strength (in dosage). Also, note that A, B, ... are just examples. In the data, each ingredient may have different strings, e,.g., A = acetaminophen, B = codeine, etc.

Now, my question is that how can I return the strength for each active ingredient for each drug_ID? For example, for drug 1, I need to return 5, 10, and 5 mg for A, B, and C, respectively. Any help/idea is appreciated!

[Shmuel]

You can use SCAN function on both variables.

Please post how do you want to format your output ?

[Alireza_Boloori]

Thanks! I used a combination of SCAN and INDEX to find my solution.

[Jagadishkatam]

you could try something as below

data have;
input drug_ID$   drug_name$ 16- 22  strength$ 34-49;
cards;
1              A/B/C             5 mg-10 mg-5 mg
2              D                   10 mg
3              E/F                7 mg-15mg
4              E/B               13 mg-8 mg
;


data want;
set have;
do i=1 to countw(drug_name,'/');
drug=scan(drug_name,i,'/');
dose=scan(strength,i,'-');
output;
end;
drop i;
run;