BookmarkSubscribeRSS Feed
Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

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

5 REPLIES 5
stat_sas
Ammonite | Level 13

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
Quartz | Level 8 Kc2
Quartz | Level 8

Thank you

Ksharp
Super User
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
Quartz | Level 8 Kc2
Quartz | Level 8

thank you

rcwright
Calcite | Level 5

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1486 views
  • 0 likes
  • 4 in conversation