Solved
Contributor
Posts: 28

# Parsing Comma Separated Values

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

Accepted Solutions
Solution
‎10-04-2016 07:10 PM
Super User
Posts: 6,931

## Re: Parsing Comma Separated Values

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;

All Replies
Solution
‎10-04-2016 07:10 PM
Super User
Posts: 6,931

## Re: Parsing Comma Separated Values

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;

Frequent Contributor
Posts: 121

## Re: Parsing Comma Separated Values

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;

Super User
Posts: 6,931

## Re: Parsing Comma Separated Values

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.

☑ This topic is solved.