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.
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;
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;
Could you suggest me if description column is having special characters then
ex; A,B,C,D
B&C
E,F;C
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.