How to split one string into multiple rows?

[jixyanne]

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!

[RW9]

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;

[Linlin]

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;

[Haikuo]

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;

     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;

[stat_sas]

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;

[slchen]

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;

[jixyanne]

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

[jixyanne]

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

[stat_sas · Accepted solution]

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;

[jixyanne]

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

[Ksharp]

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