Report the latest date if two consecutive primary IDs are the same

[yelena]

Dear all,

I have the following data:

Year         GVKEY        CDL          Date                 Exposure

2005            1                 5             02-12-2005           10

2005            2                 2              02-12-2005           10

2005            3                 1              02-12-2005           55

2006            1                 4              02-12-2006            20

2006             2                3              02-12-2006            40

2006            2                 3              02-11-2006            40

2006            2                 1              02-12-2006             10

I need to know the total and average exposure per year. But if the GVKEY = CDL, I only want to report one exposure for the latest date observed. That is, for 2006, it should be on 02-12-2006. So my final output should look like this:

Year         GVKEY        CDL          Date                 Exposure

2005            1                 5              02-12-2005           10

2005            2                 2              02-12-2005           10

2005            3                 1              02-12-2005           55

2006            1                 4              02-12-2006            20

2006            2                3              02-12-2006            40

2006            2                 1              02-12-2006            10

So I am not sure how to write this code. Of course, dates can vary. But this is an idea. And then I need to calculate total exposure and average exposure per each year.

Does anyone know how the code would look like? Thank you for your help.

Yelena

[sh0e]

Will this work for you?

proc sort data = in;
  by year GVKEY date;
run;

data out;
  set in;
  by year GVKEY;
  if last.GVKEY;
  run;

[yelena]

Thank you, but it does not work. It completely eliminates the row for 2006 with the following entries:

2006 2 3 02-12-2006 40

[sh0e · Accepted solution]

Sounds like you want CDL in the sort.  For example,

proc sort data = in;
  by year GVKEY CDL date;run;

data out;  
  set in;  
  by year GVKEY CDL;  
  if last.CDL;  
  run;

[yelena]

Thank you! It works now!