Desktop productivity for business analysts and programmers

How to count distinct by two variables using proc sql

Reply
Occasional Contributor SLK
Occasional Contributor
Posts: 13

How to count distinct by two variables using proc sql

Hi,

 

My variables: ID, year, drinks(beer or wine).

 

I want to count distinct ID by the drinks variable: only count the ID-numbers who only drank beer and those who only drank wine. If a person drank both, they should not be in any count.

 

My code:

 

proc sql

create tables want as

select year, drinks, count(distinct ID)

from have

group by year, drinks

exit;

 

This only count distinct within each drink-type: if a person has more than one beer he is only in the count once, but if he also had wine he is also in the count for wine.

 

Hope someone can help me with my problem!!

 

Super User
Posts: 5,380

Re: How to count distinct by two variables using proc sql

I think this is hard to achieve in a single (non nested) SQL step.

What about if a person drinks beer year 1, and wine year 2?

Data never sleeps
Occasional Contributor SLK
Occasional Contributor
Posts: 13

Re: How to count distinct by two variables using proc sql

They only have to be unique per year so a person can drink beer one year and wine the other.

Ask a Question
Discussion stats
  • 2 replies
  • 194 views
  • 0 likes
  • 2 in conversation