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
proc sql;
create table abc as
select * from cff
group by id, Forecastpreiod
having timeofforecast=max(timeofforecast);
quit;
proc sql;
create table abc as
select * from cff
group by id, Forecastpreiod
having timeofforecast=max(timeofforecast);
quit;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.