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;
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;
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.
Since your GROUP BY is different I'm not sure that's possible within a SQL step.
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 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.
Thank you! This was sooo helpful!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.