Ranking ina rotated order

[emma78]

Hello,

I have a problem and I`m sure you have  a smart solution:-)

There are some variables with a relative timestamp. It starts with 0, each variable stands for a page in an online survey. After sending the page the relative timestamp is saved. For example  Id 1 starts with 0, and page 1 has been sending on timestamp 2, that means ID1 needs 2 seconds to send page 1. ID 1 finally ends on timestamp 14, so it takes 14 seconds to participate in the survey.

ID START page1 page2 page3 page4 page5  END
1 0           2         7         4        14         12       14
2 0           4         8        12        14         10       14

The task is to calculate the duration for each page.


It would be quite easy to calculate the difference if the timestamps were in ascending order.
But unfortunately the pages are displayed in a rotated order.
For ID1 e.g.: pages: 1,3,2,5,4
Id1: Page 1 2seconds, page 2 3 seconds, page 3 2 seconds, page 4 2 seconds, page 5 5 seconds


An old solution was to save the ranking in new variables first and but it was very error-prone, so new input is highly appreciated:-)

[Andy W]

Here is one example, it is simpler just to reshape the data to long format, then sort based on ID + timestamp and calculate the duration.

*************************************.
DATA LIST FREE /ID START page1 page2 page3 page4 page5  END.
BEGIN DATA
1 0          2        7        4       14        12       14
2 0          4        8       12       14        10       14
END DATA.
DATASET NAME Page.

VARSTOCASES /MAKE Time FROM START TO END
            /INDEX Page (Time).
SORT CASES BY ID Time.
SPLIT FILE BY ID.
CREATE Dur = DIFF(Time,1).
SPLIT FILE OFF.

*You could reshape to wide, but probably whatever stats you want are.
*easier in long format, eg.
SORT CASES BY Page.
SPLIT FILE BY Page.
FREQ Dur /FORMAT = NOTABLE /STATISTICS = MIN MAX MEAN STDEV /NTILES = 4 /HISTO.
SPLIT FILE OFF.
*************************************.

Also see the LAG function. (Also above presumes no exact ties, but hopefully with time stamps a no zero durations that is not a problem.)