09-04-2013 04:44 PM
I have a dataset with counts of a number of different places e.g.
south shields 3
north shields 14
I'm wanting to combine a number of the rows together as they're in the same area, and also want the new single row to display the sum of all of the counts put together.
For example: wallsend, heaton, north shields and newcastle could all be classed simply as newcastle with a count of (24+85+12+14)
Is there a way to do this?
Also if the data was all a similar name such as: 1 newcastle
3 newcastle united
4 newcastle council
as an example is there also a way to combine all these entries and the sum of there counts into one row named newcastle????
Hope you can help
09-04-2013 05:08 PM
create table want as
select ( case when CITY in ('Wallsend', 'Heaton', . . . .more names . . . ) then 'Newcastle'
when CITY in (. . . . some other towns to group together ) then 'Something else'
. . . . more when statements . . .
else 'All others'
end ) as town_grp
group by town_grp
This won't address your need to group similiarly-spelled towns together. For this, you could consider using the SOUNDEX, SPEEDIS, COMPLEV, or COMPGED functions.
Here is one link (of many on the web) giving an overview of the different comparison functions available in SAS.