DATA Step, Macro, Functions and more

Parsing Comma Separated Values

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

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: 5,071

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;

View solution in original post


All Replies
Solution
‎10-04-2016 07:10 PM
Super User
Posts: 5,071

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: 87

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: 5,071

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.

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

Discussion stats
  • 3 replies
  • 314 views
  • 1 like
  • 3 in conversation