eliminate old observations and have a latest observation

[JPARK]

Hi,

I am trying eliminate old observations and have a latest observation about a forecast period.

My data set looks like this.

ID Forecast preiod  time of forecast

A  12/31/2001          10/18/2001

A  12/31/2001          11/15/2001

A  12/31/2001          12/20/2001

A  12/31/2002          11/14/2002

A  12/31/2002          12/19/2002

I want to get data like this.

ID Forecastpreiod     timeofforecast

A  12/31/2001          12/20/2001

A  12/31/2002          12/19/2002

I came up with below code, but it does not work.. would you help me how to fix this problem?

sql; create table abc as

select I.*
from cff as I
group by ID Forecastpreiod

on timeofforecast=max(timeofforecast);quit;

Thank you so much

[stat_sas · Accepted solution]

proc sql;
create table abc as
select * from cff
group by id, Forecastpreiod
having timeofforecast=max(timeofforecast);
quit;

[Reeza]

Use a data step instead, especially if your data is sorted already.

data want;

set have;

by id forecast_period;

if last.forecast_period;

run;

For your SQL you're missing a comma in your group by clause and need to uses the have clause instead of ON.

proc sql;

create table abc as

select I.*
from cff as I
group by ID Forecastpreiod

having timeofforecast=max(timeofforecast);

quit;

[Haikuo]

If you data is presorted by ID fcast,

data have;

input id$ (fcast dcast) (:mmddyy10.);

format fcast dcast mmddyy10.;

cards;

A 12/31/2001          10/18/2001

A 12/31/2001          11/15/2001

A 12/31/2001          12/20/2001

A 12/31/2002          11/14/2002

A 12/31/2002          12/19/2002

;

data want;

update have(obs=0) have;

by id fcast;

run;

Haikuo