Calculating Epoch end date

[Kc2]

Hello,

I am trying to calculate the epoch end date of an element as the startdate-1 one the next element.

my starting data  looks like

USER     ELEMENT      EPOCH_START _DATE   EPOCH_END _DATE

1               A1              1-OCT-2014

1               A2               10-OCT-2014

1               A3              20-OCT-2014

2               A1              5 -OCT-2014

2               A2             15-OCT-2014

2               A3             25-OCT-2014

3               A1              8-OCT-2014

4              A1               11-OCT2014

4             A2                 21-OCt-2014

I want my end data to look like:

End date  = start date of the next element-1

USER     ELEMENT      EPOCH_START _DATE   EPOCH_END _DATE

1               A1               1-OCT-2014                       9- OCT -2014

1               A2               10-OCT-2014                     19- OCT-2014

1               A3              20-OCT-2014                       

2               A1              5 -OCT-2014                      14-OCT-2014

2               A2             15-OCT-2014                        24-OCT 2014

2               A3             25-OCT-2014

3               A1              8-OCT-2014                      

4              A1               11-OCT2014

4             A2                 21-OCt-2014

Anyone has any suggestions?

thanks

[stat_sas]

proc sort data=have;
by user descending element;
run;

data want;
set have;
by user;
format EPOCH_END_DATE date11.;
EPOCH_END_DATE=lag(EPOCH_START_DATE)-1;
if first.user then EPOCH_END_DATE=.;
run;

proc sort data=want;
by user element;
proc print data=want;
run;

[Kc2]

Thank you

[Ksharp]

data have;
input USER     ELEMENT  $    EPOCH_START_DATE : date11.  ;
format EPOCH_START_DATE date11.;
cards;
1               A1              1-OCT-2014
1               A2               10-OCT-2014
1               A3              20-OCT-2014
2               A1              5-OCT-2014
2               A2             15-OCT-2014
2               A3             25-OCT-2014
3               A1              8-OCT-2014
4              A1               11-OCT2014
4             A2                 21-OCt-2014
;
run;

data want(drop=_:);
 merge have have(firstobs=2 keep=USER EPOCH_START_DATE rename=(USER=_USER EPOCH_START_DATE=EPOCH_END_DATE));
 if USER=_USER then EPOCH_END_DATE=EPOCH_END_DATE-1;
  else      EPOCH_END_DATE=.;
run;
 

Xia Keshan

[Kc2]

thank you

[rcwright]

Second data step gets what you want.

Hopefully enough comments so you can understand what I'm doing

Shows a couple of useful techniques.

DATA test;

INPUT user : $1. element : $2. epoch_start_date : DATE9.;

FORMAT epoch_start_date DATE.;

CARDS;

1 A1 1OCT2014

1 A2 10OCT2014

1 A3 20OCT2014

2 A1 5OCT2014

2 A2 15OCT2014

2 A3 25OCT2014

3 A1 8OCT2014

4 A1 11OCT2014

4 A2 21OCT2014

;;;;

DATA results;

SET test NOBS=_no;  /* NOBS contains number of observations */

IF _n_ < _no THEN DO;  /* don't want to go past End of file */

  P = _N_+1;  /* set the point variable */

  SET test(RENAME=(user=_u element=_e epoch_start_date=epoch_end_date)) POINT=P ; /* read second stream, using POINT SET option */

  epoch_end_date = IFN (user=_u,Sum(epoch_end_date,-1),.); /* test that we are reading the same user if */

  OUTPUT;  /* write out */

  Call Missing(epoch_end_date); /* clear out end date, otherwise it carries over */

END;

ELSE OUTPUT; /* write out last obs */

DROP _:; /* drop all _ variables */

RUN;