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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.