I have multiple data in one column separated by a comma

[sarahsasuser]

I receive data in the following format:

ID#            Buildings             Other

20              23, 3456, 34       10

45              101, 234, 245      230

I want to transform the dataset so that there is 1 record per id and building combination, which would look like this:

ID#            Buildings             Other

20              23                      10

20              3456                   10

20              34                      10

45              101                    230

45              234                    230

45              245                    230

How do I do that?

[Reeza · Accepted solution]

data want;

set have;

i=1;

do while(scan(buildings, i, ",") ne ' ');

new_buildings=scan(buildings, i, ", ");

output;

i+1;

end;

drop buildings;

run;