Trying to Count & Produce Multiple Names from Variables

[dellerrrr]

Hello everyone,

Currently I am struggling with a problem I cannot seem to resolve. I am trying to see the frequency that different antidepressants are appearing in the dataset along with if there are observations with more than one antidepressant. Every observation will have SubstanceName1-SubstanceName30 and Class1-Class30. Below is some sample data of how it appears in the set. 

Sample Data

ID#      SubstanceName1      SubstanceClass1     SubstanceName2       SubstanceClass2       

1          Trazodone                  Antidepressant         Fluoxetine                      Antidepressant

2          Heroin                        Opiate                      Trazodone                      Antidepressant              

Currently the array code I am using below will only return the name of an antidepressant that appears last if there are multiple. Is there any way to count if there are observations with multiple antidepressants like the ID#1, and if so to return both Substance Names? Thank you for any guidance. 

Data depress;
set set_1;

Array AntiD $ SubstanceClass1-SubstanceClass30;
Array Name $ SubstanceName1-SubstanceName30;
do i= 1 to 30;
if AntiD(i) = 'Antidepressant' then Depression = Name(i);
end;
run;

[PaigeMiller]

It seems like this will work:

Data depress;
    set set_1;
    Array AntiD $ SubstanceClass1-SubstanceClass30;
    Array Name $ SubstanceName1-SubstanceName30;
    count=0;
    do i= 1 to 30;
        if AntiD(i) = 'Antidepressant' then Depression = Name(i);
        count=count+1;
        output;
    end;
run;

[dellerrrr]

PaigeMiller,

Unfortunately this has Depression return as blank,
& count has simply counted from 1 to 30 down the data and then resets at the 31st observation

[PaigeMiller · Accepted solution]

My mistake, how about this

Data depress;
    set set_1;
    Array AntiD $ SubstanceClass1-SubstanceClass30;
    Array Name $ SubstanceName1-SubstanceName30;
    count=0;
    do i= 1 to 30;
        if AntiD(i) = 'Antidepressant' then do;
            Depression = Name(i);
            count=count+1;
            output;
        end;
    end;
run;

[dellerrrr]

Paige Miller,

Awesome! Thank you. Do you have any thoughts on how to return those antidepressant names where there are multiple? I am trying to get a true measure of the distribution of them.

Thank you

[Reeza]

Can you restructure your data? If it's in a long form, this is much easier to answer these types of questions.

[PaigeMiller]

---

@dellerrrr wrote:
Paige Miller,

Awesome! Thank you. Do you have any thoughts on how to return those antidepressant names where there are multiple? I am trying to get a true measure of the distribution of them.

Thank you

---

I don't know what this means. Show me the exact output you want.

[dellerrrr]

I was looking to return the substance names total. So if there is only one match just one name, and if more than return multiple. I tranposed the data like Reeza suggested and have figured it out.

Thank you!

[Reeza]

If you're in a long format, SQL will do 'aggregations' on character variables and/or count distinct which can make life a lot easier to figure out the number of distinct drugs a client is using.