Hello,
I would like to seek for your help, I need to separate the column data into multiple Rows.
Sample Data:
Column 1 Column 2 Column 3
Apple Water Lunch | Dinner
Result Data:
Column 1 Column 2 Column 3
Apple Water Lunch
Apple Water Dinner
This may get you started
data want; set have; oldcolumn3= column3; do i= 1 to (countw(oldcolumn3)); column3 = scan(oldcolumn3, i,' |'); output; end; drop i oldcolumn3; run;
if you have words with embedded blanks you may need to change the ' |' to '|'
This may get you started
data want; set have; oldcolumn3= column3; do i= 1 to (countw(oldcolumn3)); column3 = scan(oldcolumn3, i,' |'); output; end; drop i oldcolumn3; run;
if you have words with embedded blanks you may need to change the ' |' to '|'
Same concept I just did do while.
data output;
set filename;
oldcol3=col3;
do while(scan(oldcol3,i,"|") ne "");
col3=scan(oldcol3,i,'|');
i+1;
output;
end;
drop oldcol3 i;
run;
if everything is in same pattern all the time then this should work
data abc; informat fruit fruit1 fruit2 $20.; input fruit fruit1 fruit2 $; datalines; orange apple mango|banana ; proc sql; select fruit, fruit1, scan(fruit2,1,'|') from abc union all select fruit, fruit1, scan(fruit2,2,'|') from abc;
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.