Help using Base SAS procedures

Separating a variable

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

Separating a variable

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'


Accepted Solutions
Solution
‎12-21-2012 06:20 PM
PROC Star
Posts: 7,363

Re: Separating a variable

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=_Smiley Happy;

  set have;

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

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

  number=substr(district,_pos);

run;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Separating a variable

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.

Super Contributor
Posts: 1,636

Re: Separating a variable

using scan function:

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

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

Contributor
Posts: 57

Re: Separating a variable

Awesome!  Thank you this worked beautiflly! 

Super User
Posts: 10,500

Re: Separating a variable

Check the results for

'Alliance College-Ready Academy - 0165866'

The inital responses may yield

name = Alliance College

number= Ready Academy

Solution
‎12-21-2012 06:20 PM
PROC Star
Posts: 7,363

Re: Separating a variable

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=_Smiley Happy;

  set have;

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

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

  number=substr(district,_pos);

run;

Contributor
Posts: 57

Re: Separating a variable

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 218 views
  • 8 likes
  • 4 in conversation