SAS: Spliting data groups into its own rows

Question

I'm new to SAS and found myself having to use it for my new job. So this is a basic question.

I have a table which looks like this:

COMPANY | Employee
--------|----------
COMP1   | @,@,@,@
COMP2   | @
COMP3   | @,@
...

Every @ is an email adress - divided by commas - of an employee in the company in the first row. Now I want to use Data step to give every email its own row. Each with the corresponding company name in the first row. Like this:

COMPANY | Employee
--------|----------
COMP1   | @
COMP1   | @
COMP1   | @
COMP1   | @
COMP2   | @
COMP2   | @
...

I hope this is enough to go on.

Thank you in advance Jan

Answer 1

Code below should solve your problem. Please note that I used 50 symbols to store the email, if you think that you might encounter longer emails increase the length in the second data step. First data step is just an example which I used for testing.

data tmp1;
    length COMP EMAIL $50.;

    comp="CMP1";
    email="[email protected], [email protected], [email protected]";
    output;

    comp="CMP2";
    email="[email protected], [email protected]";
    output;
run;

data tmp2;
    set tmp1;
    i=1;
    length new_email $50;

    do while(scan(email, i, ",") ne "");
        new_email = strip(scan(email, i, ","));
        i+1;
        output;
    end;
    drop email i;
run;