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

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'

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

6 REPLIES 6
ballardw
Super User

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.

Linlin
Lapis Lazuli | Level 10

using scan function:

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

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

SannaSanna
Quartz | Level 8

Awesome!  Thank you this worked beautiflly! 

ballardw
Super User

Check the results for

'Alliance College-Ready Academy - 0165866'

The inital responses may yield

name = Alliance College

number= Ready Academy

art297
Opal | Level 21

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;

SannaSanna
Quartz | Level 8

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1028 views
  • 8 likes
  • 4 in conversation