Parsing Comma Separated Values

[dhrumil_patel]

I have a character column containing comma-separated numbers, and the list of numbers is of varying length. For example,

ColumnName

72,748

980

37449,37451,37452,37453,37454

70286,70287,70288,70290,70291,70292,70293

....

I am trying to parse ColumnName by putting each number in a separate column. To illustrate,

Parse1     Parse2     Parse3     Parse4

72            748

980

37449      37451       37452      37453      ......

70286      70287       70288      70290      ......

In a DATA step, I am parsing the comma separated values using the SCAN function.

data b; set a;

parse1 = scan(ColumnName, 1, ",");

parse2 = scan(ColumnName, 2, ",");

...

run;

However, I do not know the maximum number of values in the list. So I don't know how many parseN variables to define. Is there a function that can read the list of values in each record and return the max number of values for all the records? 

Thank you.

Dhrumil Patel

[Astounding · Accepted solution]

It will take two steps, not a single DATA step.  Here's one way:

data b;

set a;

rownum = _n_;

length parse $ 5;

do i=1 by 1 until (parse=' ');

   parse = scan(ColumnName, i, ',');

   if parse > ' ' then output;

end;

keep parse rownum;

run;

proc transpose data=b out=want (drop=_name_) prefix=parse;

by rownum;

var parse;

run;

[devsas]

Amazing! Astounding can you please explain the the logic behind your first step-especially do i=1 by 1, i have never seen something like this before. Thanks.

---

@Astounding wrote:

It will take two steps, not a single DATA step.  Here's one way:

data b;

set a;

rownum = _n_;

length parse $ 5;

do i=1 by 1 until (parse=' ');

   parse = scan(ColumnName, i, ',');

   if parse > ' ' then output;

end;

keep parse rownum;

run;

---

[Astounding]

do i=1 by 1 until (some condition);

This starts i at 1, and adds 1 each time through the loop.  It just doesn't set an upper limit for ending the loop, relying on the DO UNTIL condition to eventually become true.