Topic
• 6 replies
• Latest Post - ‏2013-01-04T23:28:26Z by SystemAdmin
445 Posts

# 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
445 Posts

#### Re: Calculating average number of days between drug tests

‏2013-01-04T21:02:50Z
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.
445 Posts

#### Re: Calculating average number of days between drug tests

‏2013-01-04T21:45:54Z
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.
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
445 Posts

#### Re: Calculating average number of days between drug tests

‏2013-01-04T21:57:13Z
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
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.
445 Posts

#### Re: Calculating average number of days between drug tests

‏2013-01-04T22:17:01Z
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.
Thanks very much
445 Posts

#### Re: Calculating average number of days between drug tests

‏2013-01-04T22:24:22Z
Thanks very much
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.
445 Posts

#### Re: Calculating average number of days between drug tests

‏2013-01-04T23:28:26Z
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.
I don't see any messages - just syntax. I suggest that you read up on the dataset commandss in the Command Syntax Reference.