BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jixyanne
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Ammonite | Level 13

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
Lapis Lazuli | Level 10


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
Calcite | Level 5

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

jixyanne
Calcite | Level 5

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
Ammonite | Level 13

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
Calcite | Level 5

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

Ksharp
Super User
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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Health and Life Sciences Learning

 

Need courses to help you with SAS Life Sciences Analytics Framework, SAS Health Cohort Builder, or other topics? Check out the Health and Life Sciences learning path for all of the offerings.

LEARN MORE

Discussion stats
  • 10 replies
  • 48625 views
  • 8 likes
  • 7 in conversation