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
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
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
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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: