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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.