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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.