Solved
New Contributor
Posts: 2

# Split string into rows using delimeter

Hi All,

Thanks for any help in advance.

I have a dataset that is like this:

Key   Width

123   99,99

456   99

789   87,99,142

What I wish I had was the following:

Key   NewWidth    Number

123   99                1

123   99                2

456   99                1

789   87                1

789   99                2

789   142              3

I have searched a few of the messages and gotten some things to work but they require the NewWidth to always be the same # of characters...which it can be 2 or 3 characters.  So far I have attempted using substr(), scan(), countc()...nothing has clicked.

Again, thanks for any pointers!!!

Accepted Solutions
Solution
‎09-25-2015 06:23 AM
New Contributor
Posts: 2

## Re: Split string into rows using delimeter

Well - turns out I should have just kept trying!!  Just solved it...

data test (keep=Key NewWidth Number);

set a.datasetname;
count=count(width,',');
put count=;

do i=1 to (count+1);
NewWidth=scan(width,i,',');
Number=i;
output;
end;
run;

All Replies
Solution
‎09-25-2015 06:23 AM
New Contributor
Posts: 2

## Re: Split string into rows using delimeter

Well - turns out I should have just kept trying!!  Just solved it...

data test (keep=Key NewWidth Number);

set a.datasetname;
count=count(width,',');
put count=;

do i=1 to (count+1);
NewWidth=scan(width,i,',');
Number=i;
output;
end;
run;

Super Contributor
Posts: 275

## Re: Split string into rows using delimeter

``````data want;
set have;
number=0;
do i=1 by 1;
newwidth=scan(width,i);
if not missing(newwidth) then do;
number+1;
output;
end;
if missing(newwidth) then leave;
end;
drop i width;
run;
``````
Posts: 5,519

## Re: Split string into rows using delimeter

It is not clear whether newWidth should be character or numeric. Here is a way to get both:

``````data want;
set have;
do number = 1 by 1;
newWidth = scan(width, number, ",");
if missing(newWidth) then leave;
numWidth = input(newWidth, best.);
output;
end;
keep key newWidth number numWidth;
run;``````
PG
🔒 This topic is solved and locked.