topic Re: split one string into multiple row in SAS Programming

split one string into multiple row

pritikhaire84 — Thu, 02 Nov 2017 07:46:59 GMT

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.

Re: split one string into multiple row

Kurt_Bremser — Thu, 02 Nov 2017 08:08:21 GMT

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

Re: split one string into multiple row

ShiroAmada — Thu, 02 Nov 2017 08:29:20 GMT

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;

Re: split one string into multiple row

ballardw — Thu, 02 Nov 2017 14:24:40 GMT

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?

Re: split one string into multiple row

ballardw — Thu, 02 Nov 2017 14:25:41 GMT

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

Re: split one string into multiple row

pritikhaire84 — Fri, 03 Nov 2017 05:51:39 GMT

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