## split one string into multiple row

Solved
Occasional Contributor
Posts: 6

# split one string into multiple row

``````Hi Team,
i Need help, Want to split string data (Delimiter is comma )and insert it in new row
I have data like -
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:
1	C. M&M Process
2	B. Supplier Design

for the second row result should be like:
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

Accepted Solutions
Solution
‎11-03-2017 01:50 AM
Super User
Posts: 9,890

## Re: split one string into multiple row

In a data step:

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎11-03-2017 01:50 AM
Super User
Posts: 9,890

## Re: split one string into multiple row

In a data step:

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 113

## Re: split one string into multiple row

``````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;``````
Super User
Posts: 13,321

## Re: split one string into multiple row

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

Super User
Posts: 13,321

## Re: split one string into multiple row

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?

Occasional Contributor
Posts: 6

## Re: split one string into multiple row

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

☑ This topic is solved.