Is there a way in PROC SQL to split a string that contains commas so that the end result is displayed as a single value on each row?
example:
Variable1
1111,2222,3333,4444,
6666,7777,8888,
5555,9999
Need to return as:
Variable1
1111
2222
3333
4444
6666
7777
8888
5555
9999
I tried using a scan function, however have not had much luck. Any help would be greatly appreciated. Thank you in advance.
data have ;
var1 = '1111,2222,3333,4444,6666,7777,8888,5555,9999';
run;
Easy enough with a data step.
data want ;
set have;
save=var1 ;
do i=1 to countw(save,',');
var1=scan(save,i,',');
output;
end;
drop i save ;
run;
Pain in the neck in PROC SQL. What is the upper limit on the number of values that could be in the variable? You need to code for all of them.
proc sql;
create table want as
select scan(var1,1,',') as var1 from have
union select scan(var1,2,',') as var1 from have where calculated var1 is not null
union select scan(var1,3,',') as var1 from have where calculated var1 is not null
....
;
quit;
Do you need to do this in proc sql?
This would be very easy in a data step, and as far as I know, difficult if not impossible in PROC SQL. Your choice.
data have ;
var1 = '1111,2222,3333,4444,6666,7777,8888,5555,9999';
run;
Easy enough with a data step.
data want ;
set have;
save=var1 ;
do i=1 to countw(save,',');
var1=scan(save,i,',');
output;
end;
drop i save ;
run;
Pain in the neck in PROC SQL. What is the upper limit on the number of values that could be in the variable? You need to code for all of them.
proc sql;
create table want as
select scan(var1,1,',') as var1 from have
union select scan(var1,2,',') as var1 from have where calculated var1 is not null
union select scan(var1,3,',') as var1 from have where calculated var1 is not null
....
;
quit;
Nice illustration that it certainly can be done in SQL, but it will get really burdensome with a lot of delimited values. This also assumes that there aren't many other variables to deal with, so I'm really wondering why it has to be done in SQL?
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.