topic Re: split one string into multiple row in SAS Programming

split one string into multiple row

tunnii — Thu, 08 Jun 2017 06:32:34 GMT

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

Re: split one string into multiple row

ballardw — Thu, 08 Jun 2017 14:46:07 GMT

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

Re: split one string into multiple row

kiranv_ — Thu, 08 Jun 2017 15:01:14 GMT

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;

Re: split one string into multiple row

Peter_C — Fri, 09 Jun 2017 19:17:09 GMT

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
;

Re: split one string into multiple row

tunnii — Tue, 13 Jun 2017 02:46:04 GMT

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

Re: split one string into multiple row

tunnii — Tue, 13 Jun 2017 03:16:12 GMT

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;