topic Re: Comma delmitied variable to multiple rows in SAS Procedures

Comma delmitied variable to multiple rows

jerry898969 — Mon, 03 Mar 2014 21:48:35 GMT

Hello,

I have a csv file that I'm working on that list members to a club. The data is very bad and I'm trying to clean it up. The first issue is that they put multiple people to one row using commas to seperate. So the fname field could have 1 or many commas. I want to take every field and leave it the same except for FName. For example

FName LName Address

Bob, Joe, William, Dave Jones 1234 Test St

There are many more variables but this is how I need the data to render

FName LName Address

Bob Jones 1234 Test St

Joe Jones 1234 Test St

William Jones

1234 Test St

Dave Jones

1234 Test St

Thank you for any help you can give me.

Re: Comma delmitied variable to multiple rows

art297 — Mon, 03 Mar 2014 21:57:38 GMT

data have;

informat FName LName Address $50.;

input (FName LName Address) (&);

cards;

Bob, Joe, William, Dave Jones 1234 Test St

;

data want (drop=_:);

length FName $50;

set have (rename=FName=_FName);

_i=1;

do while (scan(_FName,_i,',') ne '');

FName=scan(_FName,_i,',');

output;

_i+1;

end;

run;

Re: Comma delmitied variable to multiple rows

jerry898969 — Mon, 03 Mar 2014 22:09:22 GMT

Hi Arthur,

Thank you so much. That worked perfectly.

You have been a great help to me and I truly appreciate it.

Thank you,

Jerry