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.
5 replies Latest Post - ‏2013-01-04T17:58:05Z by SystemAdmin
SystemAdmin
SystemAdmin
445 Posts
ACCEPTED ANSWER

Pinned topic Calculation of dates between lab tests

‏2012-12-26T18:10:24Z |
I 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 the first and last drug tests for each person, and then calculate the mean days between first and last test for all persons in the file.

I have tried the lag function various ways to do this, but cannot develop a syntax that works for this calculation.

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-04T17:58:05Z at 2013-01-04T17:58:05Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    445 Posts
    ACCEPTED ANSWER

    Re: Calculation of dates between lab tests

    ‏2012-12-26T18:34:26Z  in response to SystemAdmin
    Do you have a unique identifier for each case? If you do, I think you could sort your cases by your ID and drug test date, then use the AGGREGATE function to acquire the first and last test date for each unique case.

    DATASET DECLARE test.
    AGGREGATE
    /OUTFILE='test'
    /BREAK=id
    /DrgTstDt_Frst=FIRST(DrgTstDt)
    /DrgTstDt_Lst=LAST(DrgTstDt).

    Then you can do your date computations.
    • SystemAdmin
      SystemAdmin
      445 Posts
      ACCEPTED ANSWER

      Re: Calculation of dates between lab tests

      ‏2012-12-26T20:25:46Z  in response to SystemAdmin
      Thanks very much--It worked the first time I tried it!!

      And such a simple and smooth resolution of the problem.
      • SystemAdmin
        SystemAdmin
        445 Posts
        ACCEPTED ANSWER

        Re: Calculation of dates between lab tests

        ‏2012-12-26T21:52:42Z  in response to SystemAdmin
        Glad this worked for you.
        • SystemAdmin
          SystemAdmin
          445 Posts
          ACCEPTED ANSWER

          Re: Calculation of dates between lab tests

          ‏2013-01-04T16:41:32Z  in response to SystemAdmin
          Hi storm v:

          The solution you provided worked well for first and last lab tests for each subject.

          I need now to calculate days between each test for each subject.

          Is there a way to do this?

          Thanks again for your help.

          Tom
          • SystemAdmin
            SystemAdmin
            445 Posts
            ACCEPTED ANSWER

            Re: Calculation of dates between lab tests

            ‏2013-01-04T17:58:05Z  in response to SystemAdmin
            You could restructure your data file so you have a single line of data for each subject. Once you have all the dates associated with a single case id on one line, you can compute the difference for each test date. The easiest way to do this is to use the restructure wizard - under "Data" choose "Restructure" and follow through the steps. The wizard will also allow you to paste and modify your syntax as needed.