topic Re: Looking for PROC sql code for this patient data question in SAS Procedures

Looking for PROC sql code for this patient data question

devsas — Sun, 09 Feb 2014 02:01:39 GMT

I have a clinic data with variables patient ID, Date of appointment and Physician ID. I want to find out the cases where a specific patient came back for a follow up appointment within a week of his first appointment. If there was no further appointment for that particular patient, its a success. If the second appointment was after a week, its again a success. But, if the first appointment was followed up with another appointment within a week, it's a failure. I tried doing it with a do loop, but I'm sure there is a better way of doing it with PROC SQL. I'm attaching the data set which is already sorted with patient id.

Million thanks in advance!

Re: Looking for PROC sql code for this patient data question

Haikuo — Sun, 09 Feb 2014 05:05:31 GMT

I don't know why you insist pursuing proc sql solution, since you haven't shown your datastep code, I can't really comment. In my following test, data step is way faster (only use 1/50th of time) than my Proc sql (of course, there will be many ways of doing it using Proc sql); data step will mark the second record, while proc sql will mark the first one.

1 /*PROC SQL, SLOWWOWOW*/

2 proc sql;

3 create table want_SQL as

4 select *, CASE WHEN EXISTS (select APPOINTMENT_DATE from test1 where PATIENT_ID =

4 ! A.PATIENT_ID

6 AND APPOINTMENT_DATE between A.APPOINTMENT_DATE+1 and

6 ! a.APPOINTMENT_DATE+7)

8 THEN 'FAILURE' ELSE 'SUCCESS' END AS FLAG

9 FROM TEST1 A

10 ;

NOTE: Table WORK.WANT_SQL created, with 3260 rows and 4 columns.

11 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 1.01 seconds

cpu time 0.99 seconds

13 /*DATA STEP: FAST*/

14 PROC SORT DATA=TEST1 OUT=TEST2;

15 BY patient_id APPOINTMENT_DATE;

16 RUN;

NOTE: There were 3260 observations read from the data set WORK.TEST1.

NOTE: The data set WORK.TEST2 has 3260 observations and 3 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

18 DATA WANT_DS;

19 DO UNTIL (LAST.PATIENT_ID);

20 SET TEST2;

21 LENGTH FLAG $ 10;

22 BY patient_id APPOINTMENT_DATE;

23 FLAG=IFC(FIRST.PATIENT_ID OR DIF(APPOINTMENT_DATE)>6,'SUCCESS','FAILURE');

24 OUTPUT;

25 END;

26 RUN;

NOTE: There were 3260 observations read from the data set WORK.TEST2.

NOTE: The data set WORK.WANT_DS has 3260 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

Haikuo

Re: Looking for PROC sql code for this patient data question

devsas — Mon, 10 Feb 2014 00:38:25 GMT

Thanks for your reply. I tried both your methods and while the Proc Sql one gave me errors and didnt run, the data step method worked and gave me a table which is not fully correct. I mean I need failure cases where the follow up appointment for a specific patient was within a week, but your result shows failure as only those cases where the follow up appointment or the next appointment was on the same day. Any other appointment was considered a success. For instance, 733 and 734 observation for the same patient is just 5 days apart, but it still calls it success. I'm not sure if there is some slight issue with your code or you just didnt understand my question correctly. The code I used earlier is below and it gave back all such cases where there was follow up appointment. Success is 1 and failure is 0.

proc sort data=vtest.test;

by PATIENT_ID APPOINTMENT_DATE;

run;

data vtest.q2;

retain adate i;

length followsuc 6;

set vtest.test;

by PATIENT_ID;

if first.patient_id then do;

i=1;

adate=APPOINTMENT_DATE;

end;

else do;

i=i+1;

if i=2 then do;

if APPOINTMENT_DATE - adate <=604800 then followsuc=0;

else followsuc=1;

output;

end;

run;

Re: Looking for PROC sql code for this patient data question

overmar — Sat, 15 Feb 2014 21:45:09 GMT

devsas,

The below code should have fixed the last issue with the day 5, as it is specified for dates between 0 and 7.

data test1a;

set desktop.test1;

apt_date = datepart(appointment_date);

format apt_date mmddyy10.;

keep patient_id apt_date;

run;

proc sort data = test1a;

by patient_id apt_date;

run;

data test1a;

set test1a;

count + 1;

by patient_id;

if first.patient_id then count = 1;

run;

data test1b;

set test1a;

where count <=2; /*This is done because there are a couple of people with more than 2 visits and the point of the exercise is only after their first visit, so eliminate all of the others*/

run;

data test1c;

set test1b;

by patient_id apt_date;

retain adate;

if first.patient_id then do;

adate = apt_date;

end;

if 0<datdif(adate,apt_date,'act/act')<=7 then do;

flag = 0;

output;

end;

else do;

flag = 1;

output;

end;

drop count adate;

run;