Help using Base SAS procedures

sql/distinct/count

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

sql/distinct/count

hi guys,'

i have a table of patient-id,date,and tests and i need to write a SQL statement that would count the number of distinct tests per patient (distinct= on a different date) but i dont actually understand what they asking me for.

Do they mean:

for id=22,count(distinct(tests))=1

for id=23,cont(distinct(tests))=5 and so on...?

or maybe sum(distinct(tests))=20

and sum(distinct(tests))=(10+50+100+120+180) or maybe neither?

22 29/12/1999 20

22 29/12/1999 20

23 01/11/1999 10

23 01/12/1999 10

23 08/12/1999 10

23 19/04/2000 50

23 20/06/2000 100

23 15/08/2000 120

23 10/09/2000 180

23 11/9/2000 180

23 12/9/2000 180

24 01/8/2001 160

24 01/8/2001 160

24 15/8/2001 160

24 01/1/2002 160

25 1/6/1999 10

25 2/6/1999 10

25 3/6/1999 10

25 19/4/2000 50

25 20/6/2000 100

25 15/8/2000 120

25 31/10/2000 180

25 28/11/2000 80

25 29/11/2000 80

25 30/11/2000 80

25 15/08/2001 160

25 01/1/2001 190


Accepted Solutions
Solution
‎05-02-2013 11:07 PM
Respected Advisor
Posts: 4,935

Re: sql/distinct/count

My guess would be that given the variables (patientId, testDate testId), the required counts would be given by:

proc sql;

create table want as

select patientId, count(*) as nbTests

from(

     select unique patientId, testDate, testId

     from have)

group by patientId;

select * from want;

quit;

                                patientId   nbTests
                                -------------------
                                       22         1
                                       23         9
                                       24         3
                                       25        12

PG

PG

View solution in original post


All Replies
Solution
‎05-02-2013 11:07 PM
Respected Advisor
Posts: 4,935

Re: sql/distinct/count

My guess would be that given the variables (patientId, testDate testId), the required counts would be given by:

proc sql;

create table want as

select patientId, count(*) as nbTests

from(

     select unique patientId, testDate, testId

     from have)

group by patientId;

select * from want;

quit;

                                patientId   nbTests
                                -------------------
                                       22         1
                                       23         9
                                       24         3
                                       25        12

PG

PG
Super Contributor
Super Contributor
Posts: 444

Re: sql/distinct/count

yea it does make sense.distinct patientid,date,test.Thanks PG

and if i want to write a SQL statement that would count the number of individuals   who have the same testid value on at least 3 consecutive test dates. What would that be,any idea?

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 300 views
  • 1 like
  • 2 in conversation