topic Calculating Epoch end date in SAS Programming

Calculating Epoch end date

Kc2 — Thu, 30 Oct 2014 19:51:39 GMT

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

Re: Calculating Epoch end date

stat_sas — Thu, 30 Oct 2014 20:18:34 GMT

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;

Re: Calculating Epoch end date

Kc2 — Thu, 30 Oct 2014 20:28:57 GMT

Thank you

Re: Calculating Epoch end date

Ksharp — Fri, 31 Oct 2014 08:19:32 GMT

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

Re: Calculating Epoch end date

Kc2 — Fri, 31 Oct 2014 13:00:36 GMT

thank you

Re: Calculating Epoch end date

rcwright — Thu, 11 Dec 2014 15:52:53 GMT

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;