topic Re: Separating a variable in SAS Procedures

Separating a variable

SannaSanna — Fri, 21 Dec 2012 22:42:36 GMT

I have a data variable that appears to have been merged with two different variables. I now need to separate the variable into two new variables. Is there a way to pull apart a variable to create two different new variables. The position location is never the same so I dont believe I can use the substring function. Here is sample:

Data element is District and it appears to have been combined with the district name and a number. I need to separate the name and number- Is it possible?

'Alliance College-Ready Academy - 0165866'

'Excel Prep Charter - 018842'

'Fortuna Elementary - 038521'

Re: Separating a variable

ballardw — Fri, 21 Dec 2012 22:51:42 GMT

irsIn a data step try:

WARNING: Assumes - is in every value.

pos = index(district,' - ');

DistrictName = substr(district,1,(pos-1));

DistrictNumber = substr(district,(pos+3));

Drop pos; /* may leave in POS while doing diagnostics */

You'll need to set the lengths of DistrictName and DistrictNumber to desired else they're likely to get truncated.

You may need pos-2 or pos+2 depending on whether there are more spaces around the hyphen.

If you have many examples with the hyphen in name as well this may get sticky trying to find the right one.

Message was edited by: Edward Ballard because initial search for single hyphen failed on example first data example.

Re: Separating a variable

Linlin — Fri, 21 Dec 2012 22:52:34 GMT

using scan function:

name=scan(district,1,'-');

number=scan(district,2,'-');

Re: Separating a variable

SannaSanna — Fri, 21 Dec 2012 22:56:16 GMT

Awesome! Thank you this worked beautiflly!

Re: Separating a variable

ballardw — Fri, 21 Dec 2012 22:57:50 GMT

Check the results for

'Alliance College-Ready Academy - 0165866'

The inital responses may yield

name = Alliance College

number= Ready Academy

Re: Separating a variable

art297 — Fri, 21 Dec 2012 23:20:07 GMT

Suzanne: You might find call scan helpful. e.g.:

data have;

input district & $255.;

cards;

Alliance College-Ready Academy - 0165866

Excel Prep Charter - 018842

Fortuna Elementary - 038521

;

data want (drop=_:);

set have;

CALL SCAN(district, -1, _pos, _len );

name=substr(district,1,_pos-3);

number=substr(district,_pos);

run;

Re: Separating a variable

SannaSanna — Fri, 21 Dec 2012 23:41:41 GMT

Thank you so much Mr. Tabachneck!! That resolved it and much thanks to ballardw for bringing that to my attention.