topic count distinct with conditions in SAS Programming

count distinct with conditions

proctice — Mon, 14 May 2018 22:34:42 GMT

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;

Re: count distinct with conditions

ballardw — Mon, 14 May 2018 22:17:28 GMT

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.

Re: count distinct with conditions

Reeza — Mon, 14 May 2018 22:18:31 GMT

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

Re: count distinct with conditions

Tom — Mon, 14 May 2018 22:32:57 GMT

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;

Re: count distinct with conditions

proctice — Tue, 15 May 2018 00:49:06 GMT

@TomBrilliant! Thank you.

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

@Reeza Fixed my typo. Sorry tor the confusion.

Re: count distinct with conditions

ballardw — Tue, 15 May 2018 16:40:16 GMT

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

Re: count distinct with conditions

kbelang1 — Thu, 14 Feb 2019 14:49:49 GMT

Thank you! This was sooo helpful!