Compare to previous record and counts

[deleted_user]

I need to create a report that will show a count of how many times a patient was seen for the same problem_code, and then find out the providers who saw the most patients with the same problem_codes.

I am able to do a count by Patient_id, but this does not help me. Could you please help???

Patient_id Date Provider Problem_code Problem_description
100 01-Jan-07 Smith 200 Arthritis
100 04-Apr-07 Doe 300 Joint_Pain
100 30-Jun-07 Colby 200 Arthritis
101 14-Feb-07 Doe 210 Back_Pain
101 07-Sep-07 Doe 210 Back_Pain
102 16-Jun-07 Mark 214 Headache
102 24-Jun-07 Smith 200 Arthritis
....

[Olivier]

Hi Nathalie.
By the way, I don't think you're posting in the right forum. That would rather be a question for the SAS PROCEDURES category.
Anyway, you can easily solve your problem with a little bit of SQL, which allows you to count, group, order, compute new variables all a the same time.
[pre]
DATA test ;
INPUT Patient_id $ Date anydtdte. Provider $ Problem_code $ Problem_description :$20. ;
FORMAT date DDMMYY10. ;
CARDS ;
100 01-Jan-07 Smith 200 Arthritis
100 04-Apr-07 Doe 300 Joint_Pain
100 30-Jun-07 Colby 200 Arthritis
101 14-Feb-07 Doe 210 Back_Pain
101 07-Sep-07 Doe 210 Back_Pain
102 16-Jun-07 Mark 214 Headache
102 24-Jun-07 Smith 200 Arthritis
;
RUN ;
PROC SQL ;
SELECT patient_id, problem_code, problem_description, count(*) AS number LABEL="# of times"
FROM test
GROUP BY patient_id, problem_code, problem_description
ORDER BY number DESC
;
SELECT provider,
COUNT(*) AS totn LABEL="Total # of visits",
COUNT(DISTINCT patient_id!!problem_code) AS problemsN LABEL="The # of distinct problems on distinct patients",
CALCULATED totn - CALCULATED problemsN AS reRuns LABEL="The # of visits for already seen combination of patients and problems"
FROM test
GROUP BY provider
ORDER BY reRuns DESC
;
QUIT ;
[/pre]
Regards.
Olivier

[deleted_user]

Thank you for your response. Very much appreciated 🙂