## split one string into multiple row

Solved
Occasional Contributor
Posts: 5

# 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
Super User
Posts: 13,338

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

All Replies
Solution
‎06-12-2017 10:29 PM
Super User
Posts: 13,338

## 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;``````

PROC Star
Posts: 503

## 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,191

## 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
• 584 views
• 3 likes
• 4 in conversation