DATA Step, Macro, Functions and more

How to divide the variable into rows

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

How to divide the variable into rows

[ Edited ]

Hii all,

i have a data like 

Empid Description
101 A,B,C,D
102 E
103 D,E,F
104 K
105 X,Y,Z

 and I want the output like

Empid Description
101 A
101 B
101 C
101 D
102 E
103 D
103 E
103 F
104 K
105 X
105 Y
105 Z

 The description is having any "," then devide that row into multiple(if comma is there between the text).

 

 

Any help would be greatly appreciated.


Accepted Solutions
Solution
‎11-07-2015 09:08 AM
Super Contributor
Posts: 275

Re: How to divide the variable into rows


data have;
x="A,B,C,D";output;
x="B&C"; output;
x="E,F;C";output;
run;

 

data want;
set have;
do i=1 by 1;
_x=scan(x,i);
if missing(_x) then return;
output;
end;
drop x;
run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,115

Re: How to divide the variable into rows

Try this:

data want;
set have(rename=(description=d));
length description $1; /* If your real items are not single characters, specify a sufficiently large length! */
do i=1 to countw(d, ',');
  description=scan(d, i, ',');
  output;
end;
drop d i;
run;
Frequent Contributor
Posts: 111

Re: How to divide the variable into rows

Could you suggest me if description column is having special characters then 

ex;  A,B,C,D

       B&C

     E,F;C

 

Solution
‎11-07-2015 09:08 AM
Super Contributor
Posts: 275

Re: How to divide the variable into rows


data have;
x="A,B,C,D";output;
x="B&C"; output;
x="E,F;C";output;
run;

 

data want;
set have;
do i=1 by 1;
_x=scan(x,i);
if missing(_x) then return;
output;
end;
drop x;
run;

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 280 views
  • 0 likes
  • 3 in conversation