BookmarkSubscribeRSS Feed
walshy
Calcite | Level 5

I have a dataset with counts of a number of different places e.g.

newcastle  24

sunderland 46

south shields 3

seaburn 67

durham 119

chester-le-street 43

wallsend 85

heaton 12

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

                                                                      2 newc

                                                                      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

Thanks

1 REPLY 1
Fugue
Quartz | Level 8

Regarding grouping:

proc sql;

     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

     from have

     group by town_grp

;

quit;

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.

http://www.nesug.org/Proceedings/nesug11/ap/ap07.pdf

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
  • 1 reply
  • 623 views
  • 0 likes
  • 2 in conversation