Solved
New Contributor
Posts: 4

How to split one string into multiple rows?

Hi,

There is a question about how to split one string into multiple rows. For example, how we can transform data A to data B.

Data A:                        Data B:

Column1                      Column1

A, B, C;                       A;

D, E;                           B;

F;                               C;

G, H, I;                        D;

J, K;                            E;

F;

G;

H;

I;

J;

K;

Thanks!

Accepted Solutions
Solution
‎06-19-2014 05:33 PM
Posts: 1,270

Re: How to split one string into multiple rows?

Data A;
infile datalines missover;
input Col1 \$ 23.;
datalines;
a-001, b-0024, c-123a
a-002, b-12
;

data b(keep=new rename=new=Col1);
length new \$8.;
set a;
do i=1 by 1 while(scan(col1,i,', ') ^=' ');
new=scan(col1,i,', ');
output;
end;
run;

All Replies
Super User
Posts: 9,448

Re: How to split one string into multiple rows?

hi,

data a;

column1="A, B, C;"; output;

column1="D, E"; output;

run;

Data b;

set a;

do I=1 to length(column1);

if substr(column1,i,1) not in (',',' ',';') then do;

new_col=substr(column1,i,1)||';';

output;

end;

end;

run;

Super Contributor
Posts: 1,636

Re: How to split one string into multiple rows?

data a;

length a \$5;

a='a,b';

output;

a='c,d,e';

output;

data want;

set a;

length b \$1;

i=1;

do while (scan(a,i) ne ' ');

b=scan(a,i);

output;

i+1;

end;

keep b;

proc print;run;

Posts: 3,167

Re: How to split one string into multiple rows?

Another trick recently learned from ,

Data A;

input Column1 \$ 10.;

_col=compress(column1,',; ');

cards4;

A, B, C;

D, E;

F;

G, H, I;

J, K;

;;;;

data want;

array temp(10) \$ 1 _temporary_;

length new \$1;

set a;

do i=1 to dim(temp);

if missing(temp(i)) then

leave;

new=temp(i);

output;

end;

keep new;

run;

Posts: 1,270

Re: How to split one string into multiple rows?

Data A;
input Col \$ 10.;
cards4;
A, B, C;
D, E;
F;
G, H, I;
J, K;
;;;;

data want(keep=new);
length new \$5.;
set a;
do i=1 by 1 while(scan(col,i,',;') ^=' ');
new=catt(scan(col,i,',;') , ';');
output;
end;
run;

Super Contributor
Posts: 275

Re: How to split one string into multiple rows?

data want;
set have;
do i=1 by 0;
new=scan(col1,i,',');
if missing(new) then leave;
output;
i+1;
end;
keep new;
run;

New Contributor
Posts: 4

Re: How to split one string into multiple rows?

Thank you so much for all your immediate responses. The problem solved. So appreciate your help!

New Contributor
Posts: 4

Re: How to split one string into multiple rows?

Hi All,

A follow up question to my original question. What if the data A is like below? How to transform data A to data B? Thank you so much!

Data A

Col1:

a-001, b-0024, c-123a

a-002, b-12

Data B

Col1:

a-001

b-0024

c-123a

a-002

b-12

Solution
‎06-19-2014 05:33 PM
Posts: 1,270

Re: How to split one string into multiple rows?

Data A;
infile datalines missover;
input Col1 \$ 23.;
datalines;
a-001, b-0024, c-123a
a-002, b-12
;

data b(keep=new rename=new=Col1);
length new \$8.;
set a;
do i=1 by 1 while(scan(col1,i,', ') ^=' ');
new=scan(col1,i,', ');
output;
end;
run;

New Contributor
Posts: 4

Super User
Posts: 10,699

Re: How to split one string into multiple rows?

```Data A;
infile datalines missover;
input Col1 \$23.;
datalines;
a-001, b-0024, c-123a
a-002, b-12
;
run;
data b;
set a;
do i=1 to countw(col1,',');
c=left(scan(col1,i,','));output;
end;
keep c;
run;
```

Xia Keshan

🔒 This topic is solved and locked.