Split string into rows using delimeter

[HeidiChi]

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!!!

[HeidiChi · Accepted solution]

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;

[slchen]

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;
	  

[PGStats]

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;