split one string into multiple row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

split one string into multiple row

Hello, 

 

I would like to seek for your help, I need to separate the column data into multiple Rows. 

 

Sample Data:

 

Column 1                  Column 2                     Column 3          

Apple                        Water                            Lunch | Dinner

 

 Result Data:

Column 1                  Column 2                     Column 3          

Apple                        Water                            Lunch

Apple                        Water                            Dinner


Accepted Solutions
Solution
‎06-12-2017 10:29 PM
Grand Advisor
Posts: 10,251

Re: split one string into multiple row

This may get you started

data want;
   set have;
   oldcolumn3= column3;
   do i= 1 to (countw(oldcolumn3));
      column3 = scan(oldcolumn3, i,' |');
      output;
   end;
   drop i oldcolumn3;
run;

if you have words with embedded blanks you may need to change the ' |' to '|'

 

View solution in original post


All Replies
Solution
‎06-12-2017 10:29 PM
Grand Advisor
Posts: 10,251

Re: split one string into multiple row

This may get you started

data want;
   set have;
   oldcolumn3= column3;
   do i= 1 to (countw(oldcolumn3));
      column3 = scan(oldcolumn3, i,' |');
      output;
   end;
   drop i oldcolumn3;
run;

if you have words with embedded blanks you may need to change the ' |' to '|'

 

Occasional Contributor
Posts: 5

Re: split one string into multiple row

[ Edited ]

Same concept I just did do while. 

 

data output;
      set filename;
      oldcol3=col3;
      do while(scan(oldcol3,i,"|") ne "");
          col3=scan(oldcol3,i,'|');
          i+1; 
          output;
       end; 

drop oldcol3 i;
run;

 

Regular Contributor
Posts: 233

Re: split one string into multiple row

if everything is in same pattern all the time then this should work

data abc;
informat fruit fruit1 fruit2 $20.;
input fruit fruit1 fruit2  $;
datalines;
orange apple mango|banana
;

proc sql;
select fruit, fruit1, scan(fruit2,1,'|') from abc
union all
select fruit, fruit1, scan(fruit2,2,'|') from abc;

 

Occasional Contributor
Posts: 5

Re: split one string into multiple row

This also work but uses more CPU time. Thank you for your help.
Valued Guide
Posts: 2,174

Re: split one string into multiple row

If this is external data to be read with INPUT the result couldn't be simpler😀

data abc;
Infile datalines dlm= ' |' truncover ;
Length fruit1-fruit3 $20.;
input fruit- fruit3 @ ;
output ;
input fruit3 $;
If fruit3 ne ' ' then output ;
datalines;
orange apple mango|banana
Lemon tangerine pear|apple
;
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 138 views
  • 3 likes
  • 4 in conversation