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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.