topic Re: eliminate old observations and have a latest observation in SAS Programming

eliminate old observations and have a latest observation

JPARK — Mon, 10 Nov 2014 18:38:56 GMT

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

Re: eliminate old observations and have a latest observation

stat_sas — Mon, 10 Nov 2014 18:48:35 GMT

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

Re: eliminate old observations and have a latest observation

Reeza — Mon, 10 Nov 2014 18:51:39 GMT

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;

Re: eliminate old observations and have a latest observation

Haikuo — Mon, 10 Nov 2014 18:53:05 GMT

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