Count case in past

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

mors26
Posts: 3
Joined: Wed Jul 25, 2012 4:28 pm

Count case in past

Postby mors26 » Wed Jul 25, 2012 4:55 pm

Hi all,
I have a DB with variable ID, TIEM_STAMP and AMOUNT, I want to create a sintax that show me case by case the numeber of case and the sum of the amout of the previous case but just the case that are in the past 1 hour (referred to the single case).
I attach the DB exemple (ID, TIME_STAMP, Amoutn) and the variables I would like to have (Count, Sum).
id ¦ time_stamp ¦ Amout ¦ COUNT ¦ SUM
1 ¦ 27-Jul-2012 08:00:00 ¦ 10 ¦ 1 ¦ 10
2 ¦ 27-Jul-2012 08:03:00 ¦ 12 ¦ 2 ¦ 22
3 ¦ 27-Jul-2012 08:59:00 ¦ 3 ¦ 3 ¦ 25
4 ¦ 27-Jul-2012 09:24:00 ¦ 78 ¦ 2 ¦ 81
5 ¦ 27-Jul-2012 09:31:00 ¦ 34 ¦ 3 ¦ 114
6 ¦ 27-Jul-2012 09:46:00 ¦ 12 ¦ 4 ¦ 124
7 ¦ 27-Jul-2012 10:57:00 ¦ 7 ¦ 1 ¦ 7
8 ¦ 27-Jul-2012 11:03:00 ¦ 10 ¦ 2 ¦ 17

I wiil be glad to have and advice, because i tried to find a solultion but i found nothing.
I hope someone can help me.
Thank you very much.
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

Re: Count case in past

Postby Penguin_Knight » Wed Jul 25, 2012 10:13 pm

Not the most elegant way because it exploits the fact that the maximal amount of lags can only be 59 since your smallest increment is 1 minute and your threshold to be grouped is 60 minutes.

Code: Select all

DEFINE ManyLags () .
COMPUTE LAG0 = 1 .
!DO !I = 1 !TO 59 .
COMPUTE !CONCAT("LAG",!I) = (((DATE - LAG(DATE, !I))/60) < 60) .
!DOEND .
COMPUTE MYCOUNT = SUM(LAG0 TO !CONCAT("LAG", !I)) .
COMPUTE AMOUNT0 = amount .
!DO !I = 1 !TO 59 .
COMPUTE !CONCAT("AMOUNT",!I) =  LAG(amount, !I) * !CONCAT("LAG",!I) .
!DOEND .
COMPUTE MYSUM = SUM(AMOUNT0 TO !CONCAT("AMOUNT", !I)) .
EXECUTE .
DELETE VARIABLES LAG0 TO !CONCAT("LAG", !I) .
DELETE VARIABLES AMOUNT0 TO !CONCAT("AMOUNT", !I) .
!ENDDEFINE .
EXECUTE .

ManyLags .
EXECUTE .
If you run this on your data, you should see that there is a calculation error in the example you posted. The last column should be:

Code: Select all

10.00
22.00
25.00
81.00
115.00
127.00
7.00
17.00
Also, I changed your variable names "sum" and "count" to "mysum" and "mycount". Both "sum" and "count" are SPSS commands and it is not a good habit to name your variables with command names used in the very software.

If you'd like to understand what were calculated, delete the two lines:

Code: Select all

DELETE VARIABLES LAG0 TO !CONCAT("LAG", !I) .
DELETE VARIABLES AMOUNT0 TO !CONCAT("AMOUNT", !I) .
and you should be able to see the concept.
mors26
Posts: 3
Joined: Wed Jul 25, 2012 4:28 pm

Re: Count case in past

Postby mors26 » Thu Jul 26, 2012 6:13 am

WOW you've been very quick in writing a solution.
I'm very happy for this and i will try it asap.

Thank you very much!!!

PS:Sorry for the caluclation error, i've done after 10 hours of work and i was a bit stoned ;)

Who is online

Users browsing this forum: No registered users and 1 guest

cron