topic Re: Compare to previous record and counts in SAS Procedures

Compare to previous record and counts

deleted_user — Tue, 15 Apr 2008 16:15:55 GMT

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
....

Re: Compare to previous record and counts

deleted_user — Tue, 15 Apr 2008 22:41:20 GMT

You could use PROC SQL to build two tables that you could get your report from

Sort of like this:

PROC SQL;
CREATE TABLE PATIENT_CODE AS
SELECT
PATIENT_ID,
PROBLEM_CODE,
COUNT(PATIENT_ID) AS TIMES
FROM
PATIENT_TABLE
GROUP BY
PATIENT_ID, PROBLEM_CODE;

PROC SQL;
CREATE TABLE PROVIDER_CODE AS
SELECT
PROVIDER,
PROBLEM_CODE,
COUNT(PROVIDER) AS TIMES
FROM
PATIENT_TABLE
GROUP BY
PROVIDER, PROBLEM_CODE
ORDER BY
PROBLEM_CODE, TIMES DESC;

Hope that gets you started.

Ike Eisenhauer

Re: Compare to previous record and counts

Cynthia_sas — Wed, 16 Apr 2008 00:01:27 GMT

Hi:
That's a nice solution. I was sort of thinking PROC TABULATE (or PROC REPORT) which would not require creating a table followed by a PROC PRINT, but would generate a report with the desired information. I did the Tabulate approach first, after creating some data (with a few extra rows):
[pre]

data ptfile;
length problem_description $15;
infile datalines;
input Patient_id Date : anydtdte9. Provider $ Problem_code Problem_description $;
return;
datalines;
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 Mark 210 Back_Pain
101 07-Sep-07 Doe 210 Back_Pain
101 08-Sep-07 Smith 210 Back_Pain
101 09-Sep-07 Colby 210 Back_Pain
102 16-Jun-07 Mark 214 Headache
102 24-Jun-07 Smith 200 Arthritis
102 25-Jun-07 Doe 200 Arthritis
102 26-Jun-07 Colby 200 Arthritis
;
run;

ods listing close;
ods html file='c:\temp\tab_method1.html' style=sasweb;

proc tabulate data=ptfile f=comma6.;
title '1a. Tabulate Approach -- One Big Table';
class patient_id provider problem_code;
table ((patient_id all='Patient Total')
(provider all='Provider Total')),
problem_code all='Total';
keylabel n = 'Count';
keyword all / style={vjust=b};
run;

proc tabulate data=ptfile f=comma6.;
title '1b. Tabulate Approach -- Two Tables';
class patient_id provider problem_code;
table (patient_id all='Patient Total'),
problem_code all='Total';

table ((provider all='Provider Total')),
problem_code all='Total';
keylabel n = 'Count';
keyword all / style={vjust=b};
run;
ods html close;

[/pre]

if you run this, you will see that the 1a) output has the information one "big" table and the 1b) output has the information in two separate tables.

cynthia

here's the proc report approach using the same data, but with "list" output instead of cross-tab output and a summary line underneath every patient_id and every provider:
[pre]

ods html file='c:\temp\report_method4.html' style=sasweb;

** not an ACROSS example;
proc report data=ptfile nowd
style(summary)=Header;
title '4a. Proc REPORT Approach -- Patient Table';
column patient_id problem_code n;
define patient_id / group;
define problem_code / group;
define n / 'Times' f=comma6.;
break after patient_id / summarize;
rbreak after / summarize;
run;

proc report data=ptfile nowd
style(summary)=Header;
title '4b. Proc REPORT Approach -- Provider Table';
column provider problem_code n ;
define provider / group;
define problem_code / group;
define n / 'Times' f=comma6.;
break after provider / summarize;
rbreak after / summarize;
run;

ods html close;
[/pre]

Re: Compare to previous record and counts

deleted_user — Thu, 17 Apr 2008 15:15:39 GMT

Wow! you guys rock!!! thank you so much for your help with this.