Solved
Contributor
Posts: 41

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

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

Accepted Solutions
Solution
‎07-06-2016 04:09 PM
Contributor
Posts: 25

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

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;

All Replies
Contributor
Posts: 25

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

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;
Contributor
Posts: 41

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

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

Solution
‎07-06-2016 04:09 PM
Contributor
Posts: 25

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

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;
Contributor
Posts: 41

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

Thank you! It works now!

☑ This topic is solved.