How to split one string into multiple rows?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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
Trusted Advisor
Posts: 1,228

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

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;

Respected Advisor
Posts: 3,156

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;

     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;

Trusted Advisor
Posts: 1,228

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
Trusted Advisor
Posts: 1,228

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

Re: How to split one string into multiple rows?

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

Super User
Posts: 9,874

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 16924 views
  • 5 likes
  • 7 in conversation