DATA Step, Macro, Functions and more

Help with count procedure.

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Help with count procedure.

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

 


Accepted Solutions
Solution
‎11-23-2017 06:27 AM
Super User
Super User
Posts: 9,866

Re: Help with count procedure.

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


All Replies
Solution
‎11-23-2017 06:27 AM
Super User
Super User
Posts: 9,866

Re: Help with count procedure.

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.

Contributor
Posts: 66

Re: Help with count procedure.


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 126 views
  • 0 likes
  • 2 in conversation