Help to identify matching cases in dataset

[Katb81]

Hi, I'm quite new to SPSS and having trouble with some analysis of duplicate cases. I am looking at data for patients receiving treatment for substance misuse. The patients may attend a treatment service multiple times before starting treatment and may be registered with more than one service at a time. I need to find out how many times the same patient attends the same service within a given time period, and how many times they attend different services. I also need to know how many of the cases are unique, i.e have only every attended one service on one occasion. In addition, I need to know how many times there is an overlap between the services, e.g how many patients does one service share with another within that time period.

Example data:

Patient ID Service_ID
1001 Service 1
1001 Service 1
1001 Service 5
1002 Service 2
1002 Service 5
1003 Service 1
1004 Service 2
1004 Service 2
end data.

I have around 4500 cases to analyse but I'm not sure how to go about this, any help would be appreciated! I can provide more data examples if this would help.

Thanks,

Kathryn

[David Marso]

Most if not all of these can be addressed with the AGGREGATE command.
Please review the documentation and post back with further questions if you get stuck.

Katb81 wrote

Hi, I'm quite new to SPSS and having trouble with some analysis of duplicate cases. I am looking at data for patients receiving treatment for substance misuse. The patients may attend a treatment service multiple times before starting treatment and may be registered with more than one service at a time. I need to find out how many times the same patient attends the same service within a given time period, and how many times they attend different services. I also need to know how many of the cases are unique, i.e have only every attended one service on one occasion. In addition, I need to know how many times there is an overlap between the services, e.g how many patients does one service share with another within that time period.

Example data:

Patient ID Service_ID
1001 Service 1
1001 Service 1
1001 Service 5
1002 Service 2
1002 Service 5
1003 Service 1
1004 Service 2
1004 Service 2
end data.

I have around 4500 cases to analyse but I'm not sure how to go about this, any help would be appreciated! I can provide more data examples if this would help.

Thanks,

Kathryn

[Katb81]

Hi,

Thanks for the advice. I have tried aggregate and can get some of the data that way. What I am having trouble with is how to determine the number of times services crossover. For each service I need to work out how many patients are registered with that service and another service at the same time,

e.g How many times does Service 1 share a patient with Service 2, and how many times with Service 3, etc. I need to work out how many times every combination of services occurs. Could you advise me on the best way to do this?

Thanks,

Kathryn

[David Marso]

Key is to first roll up each ID into a single record of services and then roll out the constituent pairs.
Follow up with a final aggregation.
Study this for some useful additions to your SPSS toolkit.

 /* simply assuming data are in long format as previously described */.
AGGREGATE OUTFILE * / BREAK Time ID service / Count=N.
COMPUTE Count=1.
CASESTOVARS ID=Time ID .

PRESERVE.
CD '%userprofile%\Desktop'.

COMPUTE @=0.
VECTOR S=Service.1 TO @.
COMPUTE #=SUM(NVALID(Service.1 TO @),NMISS(Service.1 TO @)).
/* Roll out pairs of services (retaining Time and ID) */.
LOOP ##=1 TO #-1.
+  LOOP ###=##+1 TO #.
+    COMPUTE S1=S(##).
+    COMPUTE S2=S(###).
+  XSAVE OUTFILE "tmp.sav" / KEEP Time ID S1 S2.
+ END LOOP.
END LOOP.
EXECUTE.
GET FILE "tmp.sav" .
SELECT IF NVALID(S1,S2)=2 AND S2 GT 0.
AGGREGATE OUTFILE * / BREAK Time S1 S2 / COUNT=N.
ERASE FILE "tmp.sav" .
RESTORE.

Katb81 wrote

Hi,

Thanks for the advice. I have tried aggregate and can get some of the data that way. What I am having trouble with is how to determine the number of times services crossover. For each service I need to work out how many patients are registered with that service and another service at the same time,

e.g How many times does Service 1 share a patient with Service 2, and how many times with Service 3, etc. I need to work out how many times every combination of services occurs. Could you advise me on the best way to do this?

Thanks,

Kathryn

[David Marso]

A slightly 'tidier' method using MATRIX.

AGGREGATE OUTFILE * / BREAK Time ID service / Count=N.
COMPUTE Count=1.
CASESTOVARS ID=Time ID .
COMPUTE @=0.
SET MXLOOPS 1000000.
MATRIX.
GET @ /VARIABLES Time ID.
GET S /VARIABLES service.1 TO @ /MISSING =0.
COMPUTE x={0,0}.
LOOP row=1 TO NROW(@).
+  LOOP col1=1 TO NCOL(S)-1.
+    DO IF S(row,col1) GT 0.
+      COMPUTE x(1)=S(row,col1).
+      LOOP col2=col1+1 TO NCOL(S).
+        DO IF S(row,col2) GT 0.
+          COMPUTE x(2)=S(row,col2).
+          SAVE {@(row,:),x}/OUTFILE */VARIABLES time id S1 S2.
+        END IF.
+      END LOOP.
+    END IF.
+  END LOOP.
END LOOP.
END MATRIX.
AGGREGATE OUTFILE * /BREAK Time s1 s2 / COUNT=N.

David Marso wrote

Key is to first roll up each ID into a single record of services and then roll out the constituent pairs.
Follow up with a final aggregation.
Study this for some useful additions to your SPSS toolkit.

 /* simply assuming data are in long format as previously described */.
AGGREGATE OUTFILE * / BREAK Time ID service / Count=N.
COMPUTE Count=1.
CASESTOVARS ID=Time ID .

PRESERVE.
CD '%userprofile%\Desktop'.

COMPUTE @=0.
VECTOR S=Service.1 TO @.
COMPUTE #=SUM(NVALID(Service.1 TO @),NMISS(Service.1 TO @)).
/* Roll out pairs of services (retaining Time and ID) */.
LOOP ##=1 TO #-1.
+  LOOP ###=##+1 TO #.
+    COMPUTE S1=S(##).
+    COMPUTE S2=S(###).
+  XSAVE OUTFILE "tmp.sav" / KEEP Time ID S1 S2.
+ END LOOP.
END LOOP.
EXECUTE.
GET FILE "tmp.sav" .
SELECT IF NVALID(S1,S2)=2 AND S2 GT 0.
AGGREGATE OUTFILE * / BREAK Time S1 S2 / COUNT=N.
ERASE FILE "tmp.sav" .
RESTORE.

Katb81 wrote

Hi,

Thanks for the advice. I have tried aggregate and can get some of the data that way. What I am having trouble with is how to determine the number of times services crossover. For each service I need to work out how many patients are registered with that service and another service at the same time,

e.g How many times does Service 1 share a patient with Service 2, and how many times with Service 3, etc. I need to work out how many times every combination of services occurs. Could you advise me on the best way to do this?

Thanks,

Kathryn