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;
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.
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.