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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.