Solved
Contributor
Posts: 36

# Selecting distinct Combinations of two variables

Hi I am thinking is it possible to count distinct combination of two variables, for example:

I know proc sql;

count(distinct (variable))

but it only count base on one criteria...

 person_ID condition_Id 1 1 1 2 1 1 2 2 3 1 3 1 4 2 4 1 4 2 5 1 5 2 6 1

this should give me a count of 9 (person 1 was count twice although it has 3 entries, person 3 count only 1 although it has 2 entries).

All ideas are welcome!

Accepted Solutions
Solution
‎10-24-2017 08:28 PM
Posts: 5,485

## Re: Selecting distinct Combinations of two variables

Distinct can also be used at the row level:

data test;
input person_ID condition_Id ;
datalines;
1 1
1 2
1 1
2 2
3 1
3 1
4 2
4 1
4 2
5 1
5 2
6 1
;

proc sql;
select count(*) as n
from
(select distinct person_id, condition_id
from test);
quit;
PG

All Replies
Solution
‎10-24-2017 08:28 PM
Posts: 5,485

## Re: Selecting distinct Combinations of two variables

Distinct can also be used at the row level:

data test;
input person_ID condition_Id ;
datalines;
1 1
1 2
1 1
2 2
3 1
3 1
4 2
4 1
4 2
5 1
5 2
6 1
;

proc sql;
select count(*) as n
from
(select distinct person_id, condition_id
from test);
quit;
PG
Contributor
Posts: 36

## Re: Selecting distinct Combinations of two variables

This is great! Than you!
Super User
Posts: 6,642

## Re: Selecting distinct Combinations of two variables

The SQL gurus may shoot me over this, but as long as both variables are numeric you could use:

proc sql;

select count (distinct(person_ID + condition_ID/10)) from have;

quit;

☑ This topic is solved.