split one string into multiple row

[tunnii]

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

[ballardw · Accepted solution]

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

[tunnii]

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;

[kiranv_]

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;

[tunnii]

This also work but uses more CPU time. Thank you for your help.

[Peter_C]

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
;