DATA Step, Macro, Functions and more

split one string into multiple row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

 


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

Re: split one string into multiple row

Posted in reply to pritikhaire84

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

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

View solution in original post


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

Re: split one string into multiple row

Posted in reply to pritikhaire84

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

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

Posted in reply to pritikhaire84
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

Posted in reply to ShiroAmada

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.

Super User
Posts: 13,321

Re: split one string into multiple row

Posted in reply to pritikhaire84

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 443 views
  • 2 likes
  • 4 in conversation