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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: