DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

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;

View solution in original post


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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 267 views
  • 0 likes
  • 2 in conversation