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

Hi.

 

I'm struggling a little with a count problem.

 

I have a dataset with just two variables, the variables are Name and Shop (both character)

 

The Name variable contains a list of roughly 20 people.

The Shop variable contains a list of about 100000 shop names.

 

What i'm trying to do is summarise the data (i've been trying to use proq freq) to show me the shop names where all of the people have used, so each of the 20 people have at some point been in that shop.

 

What I also want to do is a sliding scale so if there are no shops that all 20 have used then show me the shops where at least 19 of them have been in.

 

Appreciate any help

 

Stret

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Am not as familiar with proc freq as I should be, so I would do a small SQL:

proc sql; 
  create table WANT as 
  select SHOP,
         count(distinct NAME) as PEOPLE
  from   HAVE
  group by SHOP;
quit;

This will give you a table of all the shops, and the count of the people who used them.  You can proceed to sort this by PEOPLE to get the list from 20 downwards.

View solution in original post

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Am not as familiar with proc freq as I should be, so I would do a small SQL:

proc sql; 
  create table WANT as 
  select SHOP,
         count(distinct NAME) as PEOPLE
  from   HAVE
  group by SHOP;
quit;

This will give you a table of all the shops, and the count of the people who used them.  You can proceed to sort this by PEOPLE to get the list from 20 downwards.

Stretlow
Obsidian | Level 7

@RW9 wrote:

Am not as familiar with proc freq as I should be, so I would do a small SQL:

proc sql; 
  create table WANT as 
  select SHOP,
         count(distinct NAME) as PEOPLE
  from   HAVE
  group by SHOP;
quit;

This will give you a table of all the shops, and the count of the people who used them.  You can proceed to sort this by PEOPLE to get the list from 20 downwards.


 

Worked Perfectly.

 

Im not too good with Proc SQL.

 

Thanks you taking the time out.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 944 views
  • 0 likes
  • 2 in conversation