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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.