Split string into rows using delimeter

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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;

View solution in original post


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;
	  
Respected Advisor
Posts: 4,644

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.

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

Discussion stats
  • 3 replies
  • 325 views
  • 3 likes
  • 3 in conversation