I have multiple data in one column separated by a comma

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 106
Accepted Solution

I have multiple data in one column separated by a comma

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?


Accepted Solutions
Solution
‎02-22-2013 01:30 PM
Super User
Posts: 19,770

Re: I have multiple data in one column separated by a comma

Posted in reply to sarahsasuser

data want;

set have;

i=1;

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

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

output;

i+1;

end;

drop buildings;

run;

View solution in original post


All Replies
Solution
‎02-22-2013 01:30 PM
Super User
Posts: 19,770

Re: I have multiple data in one column separated by a comma

Posted in reply to sarahsasuser

data want;

set have;

i=1;

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

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

output;

i+1;

end;

drop buildings;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 175 views
  • 0 likes
  • 2 in conversation