Add variable that identifies occurrence

[rakeshvvv]

Hi Everyone,

Can some one help me with query. I have dataset with three variables ID,DATE ANDTIME....ID is identifier,date is date of collection and time of collection of sample....for every ID and Same Date:I would like to create new variable timepoint such a way that first occuarance will be assigned 'PRE' ,second occurance will be '2H' and third occurance will be '7H'...

100020005 2015-08-27 08:36
100020005 2015-08-27 11:32
100020005 2015-08-27 14:51
100020008 2015-10-01 08:23
100020008 2015-10-01 10:56
100020008 2015-10-01 15:32

140020004 2014-12-18 13:13

140020002 2014-09-30 14:45
140020002 2014-09-30 17:20

The output should look like this with new 4th variable time point....

100020005 2015-08-27 08:36  PRE
100020005 2015-08-27 11:32  2H
100020005 2015-08-27 14:51  7H
100020008 2015-10-01 08:23  PRE
100020008 2015-10-01 10:56  2H
100020008 2015-10-01 15:32  7H

140020004 2014-12-18 13:13  PRE

140020002 2014-09-30 14:45  PRE
140020002 2014-09-30 17:20  2H

Thanks in advance

Rakesh

[mohamed_zaki · Accepted solution]

data have;
input id date yymmdd10. time TIME.;
format time time.;
cards;
100020005 2015-08-27 08:36
100020005 2015-08-27 11:32
100020005 2015-08-27 14:51
100020008 2015-10-01 08:23
100020008 2015-10-01 10:56
100020008 2015-10-01 15:32
140020004 2014-12-18 13:13
140020002 2014-09-30 14:45
140020002 2014-09-30 17:20
;
run;
proc sort data=have;
by id date time;
run;
data want;
set have;
retain f;
by id date time;
if first.date then f=1;
if f=1 then point='PRE';
else if f=2 then point= '2H';
else if f=3 then point='7H';
f+1;
drop f;
run;

[RW9]

What is your reference point?  Without a reference point there is no way you can state that 8:36 is pre anything?  As you are talking about samples I will assume your dealing with pharma study data, in which case PRE would refer to dosing time.  Therefore the first task to undertake here is to merge dosing time onto your sample data, based on subject period visit.  Then compare the time of sample to the time of dosing.  if it is < dosing time you know it is pre, other wise you take the time of dosing away from the sample time and then assign based on nearest planned time window, so 2h or 7h.