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