count distinct with conditions

[proctice]

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;

[ballardw]

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.

[Reeza]

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

[Tom · Accepted solution]

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;

[proctice]

@TomBrilliant!  Thank you.

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

@Reeza Fixed my typo.  Sorry tor the confusion. 

[ballardw]

---

@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.

[kbelang1]

Thank you! This was sooo helpful!