topic Re: Split string into rows using delimeter in SAS Programming

Split string into rows using delimeter

HeidiChi — Tue, 15 Sep 2015 20:13:06 GMT

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

Re: Split string into rows using delimeter

HeidiChi — Tue, 15 Sep 2015 20:31:49 GMT

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;

Re: Split string into rows using delimeter

slchen — Tue, 15 Sep 2015 20:39:09 GMT

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;

Re: Split string into rows using delimeter

PGStats — Tue, 15 Sep 2015 20:43:46 GMT

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;