topic Re: Parsing Comma Separated Values in SAS Programming

Parsing Comma Separated Values

dhrumil_patel — Tue, 04 Oct 2016 22:40:40 GMT

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

Re: Parsing Comma Separated Values

Astounding — Tue, 04 Oct 2016 22:47:57 GMT

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;

Re: Parsing Comma Separated Values

devsas — Thu, 06 Oct 2016 17:35:31 GMT

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;

Re: Parsing Comma Separated Values

Astounding — Thu, 06 Oct 2016 20:27:49 GMT

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.