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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

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