Hey Experts,
I have a column data from my DB with a lot of values combined in a column. Each entry has multiple item ids separated by commas. I now need them in multiple columns. Any help will be appreciated.
Regards,
M
data have;
text='xx,ee,tt ';output;
text='jj,ii,ii,kk';output;
run;
proc sql;
select max(countw(text,',')) into : max from have;
quit;
data want;
set have;
array x{&max} $ 100;
do i=1 to countw(text,',');
x{i}=scan(text,i,',');
end;
drop i;
run;
Do you need to split the values into multiple columns, or into a single column in multiple rows?
(I'd prefer the second format)
Much appreciated if one can show how to split the values into a single column in multiple rows.
@SASComm1 wrote:
Much appreciated if one can show how to split the values into a single column in multiple rows.
See below @Ksharp's sample script repurposed for a single value in multiple rows.
data have;
text='xx,ee,tt ';
output;
text='jj,ii,ii,kk';
output;
run;
data want;
set have;
do i=1 to countw(text,',');
text2=scan(text,i,',');
output;
end;
drop i;
run;
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.