Splitting String that contains commas

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Splitting String that contains commas

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.

 

 

 


Accepted Solutions
Solution
‎03-28-2017 09:28 AM
Super User
Super User
Posts: 6,851

Re: Splitting String that contains commas

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;

View solution in original post


All Replies
PROC Star
Posts: 295

Re: Splitting String that contains commas

Do you need to do this in proc sql?

Trusted Advisor
Posts: 1,800

Re: Splitting String that contains commas

This would be very easy in a data step, and as far as I know, difficult if not impossible in PROC SQL. Your choice.

Solution
‎03-28-2017 09:28 AM
Super User
Super User
Posts: 6,851

Re: Splitting String that contains commas

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;
PROC Star
Posts: 295

Re: Splitting String that contains commas

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?

Contributor
Posts: 23

Re: Splitting String that contains commas

Thank you very much. Both solutions worked like a charm, however the data step is indeed the much easier option. Thanks again!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 193 views
  • 2 likes
  • 4 in conversation