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'
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;
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.
using scan function:
name=scan(district,1,'-');
number=scan(district,2,'-');
Awesome! Thank you this worked beautiflly!
Check the results for
'Alliance College-Ready Academy - 0165866'
The inital responses may yield
name = Alliance College
number= Ready Academy
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;
Thank you so much Mr. Tabachneck!! That resolved it and much thanks to ballardw for bringing that to my attention.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.