split one string into multiple row

[pritikhaire84]

Hi Team, 
i Need help, Want to split string data (Delimiter is comma )and insert it in new row
I have data like - 
Attribution:
row	Attribution
1	C. M&M Process,B. Supplier Design,
2	D. Supplier Process,B. Supplier Design,E. Hand Off
3	E. Hand Off,A. PVT
4	D. Supplier Process,
 
for the first row result should be like:
Attribution_result:
row	Attribution
1	C. M&M Process
2	B. Supplier Design
 
for the second row result should be like:
Attribution_result:
row	Attribution
1	D. Supplier Process
2	B. Supplier Design
3	E. Hand Off
like wise..
 
Note: In the attribution column maximum data will be like :
C. M&M Process,B. Supplier Design,D. Supplier Process,Service,E. Hand Off,A. PVT

Thanks in advance.

[Kurt_Bremser · Accepted solution]

In a data step:

- rename your current attribution variable to something else (eg _attribution) with a dataset option

- use the countw() function to determine the boundaries of a do loop:

do i = 1 to countw(_attribution,',');

- within the do loop, use the scan() function to extract the current "word":

attribution = scan(_attribution,i,',');

and use output;

- drop i and _attribution

[ShiroAmada]

data want;
  set have;
if indexc(src_var,",") > 1 then do;
  do i=1 to countc(src_var,",")+1;
    length new_var $50;
     new_var=scan(src_var,i,",");
output;
  end;
end;
run;

[ballardw]

---

@ShiroAmada wrote:

data want;
  set have;
if indexc(src_var,",") > 1 then do;
  do i=1 to countc(src_var,",")+1;
    length new_var $50;
     new_var=scan(src_var,i,",");
output;
  end;
end;
run;

---

May not want to output if the NEW_Var has length 0. The example data has a couple of lines that end in comma.

[ballardw]

First question that comes to mind: Do any of your values have an imbedded comma as part of the value?

Something like:

C. PDQ, Prelim,B. Supplier Design,

where the first comma would be part of the desired result?

[pritikhaire84]

No, I dont have values with including comma  but my values includes space and dot