topic Splitting variable into multiple rows in SAS Procedures

Splitting variable into multiple rows

jerry898969 — Fri, 17 Jun 2011 14:24:46 GMT

I have a variable that has a list of people delimited by "--". I want to seperate each person and make each one a row so I can sort then get a distinct list of people.

example data
Bob Jones--Fred Smith
William Feek--Cindy Simpson--Abigail Wii
David Davidson
Chris Testers--Mindy Sohow

I need this as the output
Abigail Wii
Bob Jones
Chris Testers
Cindy Simpson
David Davidson
Fred Smith
Mindy Sohow
William Feek

I was looking at using the scan function but I'm not sure how to cycle through the string.

Re: Splitting variable into multiple rows

sbb — Fri, 17 Jun 2011 14:42:05 GMT

Use a DO / END loop and use COUNTW function to count the words (in your part of the DO). You will have a LENGTH/ATTRIB, a SAS assignment where SCAN is used to parse the data-string, and an OUTPUT statement (minimum) in the DO/END code paragraph.

Scott Barry
SBBWorks, Inc.

Re: Splitting variable into multiple rows

AhmedAl_Attar — Fri, 17 Jun 2011 14:44:14 GMT

Hi,

Try using the count() function in BASE SAS.

data test (keep=name);
/* This a single string with multiple delimited names */
x='Bob Jones--Fred Smith--William Feek--Cindy Simpson--Abigail Wii--David Davidson--Chris Testers--Mindy Sohow';

/* Count number of times the delimiter occurs */
count= count(x,'--');
put count=;

/* Output each name to a separate observation */
do i=1 to (count+1);
name = scan(x,i,'--');
output;
end;
run;

proc print data=test; run;

Hope this helps,
Ahmed

Re: Splitting variable into multiple rows

jerry898969 — Fri, 17 Jun 2011 14:50:48 GMT

Scott,

You are the best. This is what I came up with and it seems to work.

data t ;
set pep (obs=5);
cnt = COUNTW(people, '--') ;
i=0;
do while(i < cnt);
i+1;
x = scan(people,i,'--') ;
output;
end;
run ;

Do you see any issues with the way i'm doing it?

Thank you again

Re: Splitting variable into multiple rows

jerry898969 — Fri, 17 Jun 2011 14:52:22 GMT

Ahmed,

thank you as well your solution works as well.

Thank you