topic Re: How to split one string into multiple rows? in SAS Health and Life Sciences

How to split one string into multiple rows?

jixyanne — Thu, 19 Jun 2014 14:57:02 GMT

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;

Thanks!

Re: How to split one string into multiple rows?

RW9 — Thu, 19 Jun 2014 15:08:16 GMT

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;

run;

Re: How to split one string into multiple rows?

Linlin — Thu, 19 Jun 2014 15:10:10 GMT

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;

Re: How to split one string into multiple rows?

Haikuo — Thu, 19 Jun 2014 15:27:23 GMT

Another trick recently learned from ,

Data A;

input Column1 $ 10.;

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

cards4;

A, B, C;

D, E;

G, H, I;

J, K;

;;;;

data want;

array temp(10) $ 1 _temporary_;

length new $1;

set a;

call pokelong(_col,addrlong(temp(1)),10);

do i=1 to dim(temp);

if missing(temp(i)) then

leave;

new=temp(i);

output;

end;

keep new;

run;

Re: How to split one string into multiple rows?

stat_sas — Thu, 19 Jun 2014 16:15:16 GMT

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;

Re: How to split one string into multiple rows?

slchen — Thu, 19 Jun 2014 16:16:29 GMT

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;

Re: How to split one string into multiple rows?

jixyanne — Thu, 19 Jun 2014 17:48:51 GMT

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

Re: How to split one string into multiple rows?

jixyanne — Thu, 19 Jun 2014 18:06:25 GMT

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

Re: How to split one string into multiple rows?

stat_sas — Thu, 19 Jun 2014 21:33:11 GMT

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;

Re: How to split one string into multiple rows?

Ksharp — Fri, 20 Jun 2014 15:05:52 GMT

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

Re: How to split one string into multiple rows?

jixyanne — Mon, 23 Jun 2014 20:37:04 GMT

Thank you so much stat@sas and Ksharp. Your answers are very helpful!