Creating sequential variable based on date criteria

[jzsworld]

Hello,

I am a graduate student in psychology and am trying to figure out how to create a sequence variable for therapy use based on whether or not a following session occurred within 7 days.

For example, I have as variables: ID and DATE, and I want to create a SEQUENCE variable if the DATE between two occasions by the user is within 7 days.

ID           DATE       SEQUENCE
1           01 JAN 2014        1
1           08 JAN 2014        1
1           20 JAN 2014        2
1           31 JAN 2014        3
2           01 FEB 2014        1
2           15 FEB 2014        2
2           19 FEB 2014        2
.
.
.

From what I have found online, it seems that I need to use the LAG function, but I've only minimal training in SPSS syntax or manipulating variables like this.

Any help is greatly appreciated.

[Bruce Weaver]

* Read in the sample data.

DATA LIST list /  ID (f2.0) DATE(date11)  SEQUENCE (f2.0).
BEGIN DATA
1           1-JAN-2014        1
1           8-JAN-2014        1
1           20-JAN-2014        2
1           31-JAN-2014        3
2           1-FEB-2014        1
2           15-FEB-2014        2
2           19-FEB-2014        2
END DATA.

DO IF ($casenum EQ 1) OR (ID NE LAG(ID)).
- COMPUTE SEQ = 1.
ELSE.
- COMPUTE SEQ = SUM(LAG(SEQ), DATEDIFF(DATE, LAG(DATE), 'days') GT 7).
END IF.
FORMATS SEQ (f2.0).
LIST.

OUTPUT:

ID        DATE SEQUENCE SEQ
 
 1 01-JAN-2014     1      1
 1 08-JAN-2014     1      1
 1 20-JAN-2014     2      2
 1 31-JAN-2014     3      3
 2 01-FEB-2014     1      1
 2 15-FEB-2014     2      2
 2 19-FEB-2014     2      2
 
Number of cases read:  7    Number of cases listed:  7

EXPLANATION

Here is the key line of syntax:

- COMPUTE SEQ = SUM(LAG(SEQ), DATEDIFF(DATE, LAG(DATE), 'days') GT 7).

In that line, "DATEDIFF(DATE, LAG(DATE), 'days') GT 7" returns a value of 1 if the date difference from the previous line is greater than 7 days; of it is 7 days or less, the value returned is 0.

HTH.

jzsworld wrote

Hello,

I am a graduate student in psychology and am trying to figure out how to create a sequence variable for therapy use based on whether or not a following session occurred within 7 days.

For example, I have as variables: ID and DATE, and I want to create a SEQUENCE variable if the DATE between two occasions by the user is within 7 days.

ID           DATE       SEQUENCE
1           01 JAN 2014        1
1           08 JAN 2014        1
1           20 JAN 2014        2
1           31 JAN 2014        3
2           01 FEB 2014        1
2           15 FEB 2014        2
2           19 FEB 2014        2
.
.
.

From what I have found online, it seems that I need to use the LAG function, but I've only minimal training in SPSS syntax or manipulating variables like this.

Any help is greatly appreciated.

[jzsworld]

Thank you! It worked. I appreciate your explanation on the syntax. It taught me things they don't teach in school.

Truly appreciated! Thanks.

[jzsworld]

I'm trying to extend the results from the syntax you provided last time into creating a consecutive use variable, but am unsuccessful. Essentially, for every sequence observed, I would like to create a time occasion. I tried different iterations of your syntax, but I'm unsuccessful.

Here is sample data:

DATA LIST list /  ID (f2.0) DATE(date11)  SEQUENCE (f2.0) OCCASION(f2.0).
BEGIN DATA
1           1-JAN-2014        1          1
1           8-JAN-2014        1          2
1           20-JAN-2014      2          1
1           31-JAN-2014      3          1
2           1-FEB-2014        1          1
2           15-FEB-2014      2          1
2           19-FEB-2014      2          2
END DATA.


I appreciate your help.

[Ruben Geert van den Berg]

Read this first: http://www.spss-tutorials.com/lag/.

If you study it carefully, you should be able to solve the riddle yourself. But if not, please share with us the syntax you've been trying, and we'll fix it for you, OK?

HTH,

Ruben

[jzsworld]

Thanks for the reference. I read it and was able to create a value for the 1st occasion of each sequence, and also occasions for the 1st sequence (1, 2, 3, ... n+1).

But where I get stuck is creating occasions for the 2nd, 3rd, 4th, etc sequences. The only value that I can generate is the first occasion, nothing else.

My syntax produces the following results:

                             
ID     DATE               SEQ   OCC
1 01-Jan-2014 1 1
1 08-Jan-2014 1 2
1 20-Jan-2014 2 1
1 31-Jan-2014 3 1
2 01-Feb-2014 1 1
2 15-Feb-2014 2 1
2 19-Feb-2014 2 .
2      22-Feb-2014        2      .

That two empty cells areSEQUENCE 2, OCCASIONS 2 and 3, but I can't figure out how to generate the correct syntax.

********

Here is my syntax:

* SAMPLE DATA.
DATA LIST list /  ID (f2.0) DATE(date11).
BEGIN DATA
1           1-JAN-2014        
1           8-JAN-2014        
1           20-JAN-2014      
1           31-JAN-2014      
2           1-FEB-2014        
2           15-FEB-2014      
2           19-FEB-2014      
2           22-FEB-2014
END DATA.

*Creating SEQUENCE variable.
DO IF ($casenum EQ 1) OR (ID NE LAG(ID)).
COMPUTE SEQ = 1.
ELSE.
COMPUTE SEQ = SUM(LAG(SEQ), DATEDIFF(DATE, LAG(DATE), 'days') GT 7).
END IF.
FORMATS SEQ (f2.0).
LIST.

*This creates the 1st occassion for each sequence.
IF $casenum EQ 1 OR (ID) NE LAG(ID) OR lag(SEQ) ne SEQ OCC = 1.
EXECUTE.

*This only creates the subsequent occasions for sequence 1, but not for other sequences. This is where I get stuck.
IF id = lag(id) EQ SEQ OCC = sum(lag(OCC),1).
EXECUTE.

****

Thanks again for your help.

[J. Salford]

Replace your last command with this:

if (id eq lag(id)) and (seq eq lag(seq)) occ = lag(occ) + 1.
exe.