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

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
PGStats
Opal | Level 21

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
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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?

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 924 views
  • 1 like
  • 2 in conversation