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
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;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.