BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dhrumil_patel
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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;

devsas
Pyrite | Level 9

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;

 


 

Astounding
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 13963 views
  • 3 likes
  • 3 in conversation