DATA Step, Macro, Functions and more

Selecting distinct Combinations of two variables

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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_IDcondition_Id
11
12
11
22
31
31
42
41
42
51
52
61

 

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
a month ago
Respected Advisor
Posts: 4,927

Re: Selecting distinct Combinations of two variables

Posted in reply to LisaYIN9309

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

View solution in original post


All Replies
Solution
a month ago
Respected Advisor
Posts: 4,927

Re: Selecting distinct Combinations of two variables

Posted in reply to LisaYIN9309

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: 29

Re: Selecting distinct Combinations of two variables

This is great! Than you!
Super User
Posts: 5,513

Re: Selecting distinct Combinations of two variables

Posted in reply to LisaYIN9309

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.

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

Discussion stats
  • 3 replies
  • 113 views
  • 2 likes
  • 3 in conversation