topic Re: Help with count procedure. in SAS Programming

Help with count procedure.

Stretlow — Thu, 23 Nov 2017 11:11:23 GMT

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

Re: Help with count procedure.

RW9 — Thu, 23 Nov 2017 11:21:58 GMT

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.

Re: Help with count procedure.

Stretlow — Thu, 23 Nov 2017 11:26:53 GMT

@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.