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.
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
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
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;
@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.
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?
No, I dont have values with including comma but my values includes space and dot
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.