DATA Step, Macro, Functions and more

count distinct with conditions

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

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
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;

View solution in original post


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

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

Discussion stats
  • 5 replies
  • 222 views
  • 1 like
  • 4 in conversation