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
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;
Thank you
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
thank you
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.