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!
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;
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;
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;
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;
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;
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;
Thank you so much for all your immediate responses. The problem solved. So appreciate your help!
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
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;
Thank you so much stat@sas and Ksharp. Your answers are very helpful!
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.