刚刚查了好久发现proc sql里面没有row_number()这个函数。。。所以不知道这样可以不?
data have;
input ID APP_DT yymmdd10. APP_AMT;
cards;
401 2013/12/21 1000
403 2014/1/21 8000
401 2015/1/21 3000
403 2013/1/21 5000
403 2015/5/21 7000
401 2014/12/21 5000
;
run;
proc sql;/*时间最大*/
create table want_max (drop = rank) as
select a.ID,a.app_dt,a.app_amt,
(select count(app_dt)
from have b
where b.app_dt >= a.app_dt
and a.id = b.id
) as rank
from have a
where calculated rank = 1
order by a.ID,a.app_dt;
quit;
proc sql;/*时间最小*/
create table want_min (drop = rank) as
select a.ID,a.app_dt,a.app_amt,
(select count(app_dt)
from have b
where b.app_dt <= a.app_dt
and a.id = b.id
) as rank
from have a
where calculated rank = 1
order by a.ID,a.app_dt;
quit;
|