Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
6 replies Latest Post - ‏2013-01-04T23:28:26Z by SystemAdmin
SystemAdmin
SystemAdmin
445 Posts
ACCEPTED ANSWER

Pinned topic Calculating average number of days between drug tests

‏2013-01-04T18:57:47Z |
have a large file of persons who have been drug-tested. The drug test dates have been arranged in chronological order, and the subjects in alpha order.

I need to calculate the number of days between each drug test for each person, and then calculate the mean days between each of the tests for each person in the file.

Thanks to storm v, I was able to calculate the number of days between the first and last tests for each person using the formula provided, to wit:

DATASET DECLARE test8.
AGGREGATE
/OUTFILE='test8'
/BREAK=LastName
/Testdate_Frst=FIRST(testdate)
/Testdate_Lst=LAST(testdate).

Would it work to just add up to 10-20 testdates (Scnd, Thrd, frth, etc??)

Thanks for your help. The following is an example of one case.

DOB DRUG TEST DATE
01/22/1992 01/11/2009
01/22/1992 01/11/2009
01/22/1992 07/27/2009
01/22/1992 07/27/2009
01/22/1992 08/11/2009
01/22/1992 08/21/2009
01/22/1992 09/15/2009
01/22/1992 10/07/2009
01/22/1992 10/22/2009
01/22/1992 11/01/2009
01/22/1992 12/10/2009
01/22/1992 12/17/2009
01/22/1992 12/17/2009
01/22/1992 12/18/2009
01/22/1992 01/02/2010
01/22/1992 01/02/2010
Updated on 2013-01-04T23:28:26Z at 2013-01-04T23:28:26Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    445 Posts
    ACCEPTED ANSWER

    Re: Calculating average number of days between drug tests

    ‏2013-01-04T21:02:50Z  in response to SystemAdmin
    Within a set of a person's records, the interval is just date - lag(date), so something like
    if (id eq lag(id)) diff = date - lag(date)
    gives you all the intervals.
    Then use aggregate breaking on id with mean as the summary statistic.
    • SystemAdmin
      SystemAdmin
      445 Posts
      ACCEPTED ANSWER

      Re: Calculating average number of days between drug tests

      ‏2013-01-04T21:45:54Z  in response to SystemAdmin
      Thanks

      I ran it this way and end up with following msg plus a msg stating that transformations are pending.

      DATASET ACTIVATE DataSet3.
      if (donor eq lag(donor)) diff = collect - lag(collect).

      And nothing is run.

      Tom
      • SystemAdmin
        SystemAdmin
        445 Posts
        ACCEPTED ANSWER

        Re: Calculating average number of days between drug tests

        ‏2013-01-04T21:57:13Z  in response to SystemAdmin
        That just means you need to execute the transformations. Use Transform > Run Pending Transformations or include an EXECUTE command.

        Transformations are not run until necessary, i.e., the next time something such as a procedure requires a data pass. This can save a lot of unnecessary passes through the data.
        • SystemAdmin
          SystemAdmin
          445 Posts
          ACCEPTED ANSWER

          Re: Calculating average number of days between drug tests

          ‏2013-01-04T22:17:01Z  in response to SystemAdmin
          Thanks very much
          • SystemAdmin
            SystemAdmin
            445 Posts
            ACCEPTED ANSWER

            Re: Calculating average number of days between drug tests

            ‏2013-01-04T22:24:22Z  in response to SystemAdmin
            I ran execute and got following DATASET CLOSED msg.

            DATASET ACTIVATE DataSet3.
            if (donor eq lag(donor)) diff = collect - lag(collect).
            execute.
            DATASET CLOSE DataSet1.

            I then ran following without DATASET ACTIVATE and nothing ran--got following msg:

            if (donor eq lag(donor)) diff = collect - lag(collect).
            execute.
            • SystemAdmin
              SystemAdmin
              445 Posts
              ACCEPTED ANSWER

              Re: Calculating average number of days between drug tests

              ‏2013-01-04T23:28:26Z  in response to SystemAdmin
              I don't see any messages - just syntax. I suggest that you read up on the dataset commandss in the Command Syntax Reference.