BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
proctice
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
ballardw
Super User

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
Super User

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

Tom
Super User Tom
Super User

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
Quartz | Level 8

@TomBrilliant!  Thank you.

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

@Reeza Fixed my typo.  Sorry tor the confusion. 

ballardw
Super User

@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
Fluorite | Level 6

Thank you! This was sooo helpful!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 29722 views
  • 4 likes
  • 5 in conversation