topic Re: How PROC SQL uses DISTINCT and GROUP by in SAS Procedures

How PROC SQL uses DISTINCT and GROUP by

KellyJade — Thu, 11 Jan 2018 15:38:18 GMT

This is a pretty basic question, but I wanted some confirmation since I haven't been able to find a clear answer.

I have a database of users of a certain services we provide in a given year. Sometimes these users return for the same service or other services. I want to find how many distinct users I have of each service so I constructed this code:

proc sql;
create table SERVICES as
select
service,
count(distinct user_id) as Num_Users
from users
where year = 2017
group by service;
quit;

I want to know if this is counting unique users of each service or unique users from the total database. For example, if person A used Service1 and Service2 are they being counted in both service groupings or are they being counted as 1 unique user? Is this the correct interpretation of this code?

Re: How PROC SQL uses DISTINCT and GROUP by

RW9 — Thu, 11 Jan 2018 15:44:14 GMT

You wouldn't want to use a where clause with a group by. In SQL you would use a having clause with a group by:

group by service having year=2017;

Or you could sub-query:

from (select * from users where year=2017)
group by service;

Re: How PROC SQL uses DISTINCT and GROUP by

ballardw — Thu, 11 Jan 2018 15:47:39 GMT

A very small data set to test with such as:

data users;
   input user_id Service;
   year=2017;
datalines;
1  1
1  2
;
run;

should fairly easily answer this.

Note that the output of the Proc SQL for that example is 2 records with a count of one each. Since there are only two records input ...

Re: How PROC SQL uses DISTINCT and GROUP by

LinusH — Thu, 11 Jan 2018 16:58:39 GMT

Sorry @RW9 I'm not sure I'm following you, why can't I filter the source data with a WHERE clause like this? Beside the logical part of it, HAVING will never use indexes for optimization.
I pretty much use HAVING only for filtering on aggregations.

Re: How PROC SQL uses DISTINCT and GROUP by

RW9 — Fri, 12 Jan 2018 08:57:20 GMT

I can't remember why exactly, must have had issues with it at some point in the past so now I totally avoid using where clauses on group by's and put them in subqueries or havings. It may just be habit from my side. Definately can't use use where for aggregates.