topic Re: Report the latest date if two consecutive primary IDs are the same in SAS Programming

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

yelena — Wed, 06 Jul 2016 18:20:34 GMT

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

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

sh0e — Wed, 06 Jul 2016 18:28:14 GMT

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;

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

yelena — Wed, 06 Jul 2016 19:01:49 GMT

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

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

sh0e — Wed, 06 Jul 2016 19:18:05 GMT

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;

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

yelena — Wed, 06 Jul 2016 20:09:50 GMT

Thank you! It works now!