eliminate old observations and have a latest observation

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

eliminate old observations and have a latest observation

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


Accepted Solutions
Solution
‎11-10-2014 01:48 PM
Trusted Advisor
Posts: 1,203

Re: eliminate old observations and have a latest observation

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


View solution in original post


All Replies
Solution
‎11-10-2014 01:48 PM
Trusted Advisor
Posts: 1,203

Re: eliminate old observations and have a latest observation

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


Grand Advisor
Posts: 17,338

Re: eliminate old observations and have a latest observation

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;

Respected Advisor
Posts: 3,124

Re: eliminate old observations and have a latest observation

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

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 187 views
  • 7 likes
  • 4 in conversation