Solved
Contributor
Posts: 43

# count distinct with conditions

[ Edited ]

Could you help me combine these two sql steps into one.  I want to count distinct claims for two separate conditions.

proc sql;

select person, count(distinct claim) as dx1claimcnt

from temp (where=(disease1=1))

group by person;

select person, count(distinct claim) as dx2claimcnt

from temp (where=(disease2=1))

group by person;

quit;

Accepted Solutions
Solution
‎05-14-2018 08:45 PM
Super User
Posts: 8,287

## Re: count distinct with conditions

[ Edited ]

CASE is how you program conditionally in SQL.

``````proc sql;
select
person
, count(distinct case when (disease1=1) then claim end) as claimcntdx1
, count(distinct case when (disease2=1) then claim end) as claimcntdx2
from temp
group by person
;
quit;``````

All Replies
Super User
Posts: 13,942

## Re: count distinct with conditions

If your variables disease1 and disease2 have values of 0, 1 or missing then this should work:

```proc sql;
select person, sum(disease1) as dx1claimcnt, sum(disease2) as dx2claimcnt
from temp
group by person;
quit;```

Which is one reason to code things 0/1 to begin with. If there are any other values than those mentioned above this will not work.

Super User
Posts: 24,025

## Re: count distinct with conditions

Since your GROUP BY is different I'm not sure that's possible within a SQL step.

Solution
‎05-14-2018 08:45 PM
Super User
Posts: 8,287

## Re: count distinct with conditions

[ Edited ]

CASE is how you program conditionally in SQL.

``````proc sql;
select
person
, count(distinct case when (disease1=1) then claim end) as claimcntdx1
, count(distinct case when (disease2=1) then claim end) as claimcntdx2
from temp
group by person
;
quit;``````
Contributor
Posts: 43

## Re: count distinct with conditions

@TomBrilliant!  Thank you.

@ballardw That solution doesn't dedup the claims before counting.

@Reeza Fixed my typo.  Sorry tor the confusion.

Super User
Posts: 13,942

## Re: count distinct with conditions

@proctice wrote:

@TomBrilliant!  Thank you.

@ballardw That solution doesn't dedup the claims before counting.

@Reeza Fixed my typo.  Sorry tor the confusion.

Data example of input and output would have likely changed my suggestion. Also, I did not "Count" anything, but summed a variable. With notes about the condition that it might work in the lack of details about your data.

As it is I could not tell whether the "group by" variable was correct (no changed) and I did not see a requirement for "dedup". Phrases like "count distinct claims for two separate conditions" are kind of imprecise without a concrete example.

☑ This topic is solved.