Any "By" or "class" statement in PROC SQL?

[Ken_oy]

Using the following code:
-------------------------------------------
-------------------------------------------
data have;
do group = 'a','b';
do value = 1,3,3,2,5,1,8;
output; end; end; run;

proc sql;
create view numbered as select *,
monotonic() as obsnum from have;
create view want as select numbered.group, numbered.value, cross.value as lookahead from numbered left join numbered as cross
on numbered.group=cross.group and numbered.obsnum+1=cross.obsnum
order by numbered.obsnum;
quit;
-------------------------------------------
-------------------------------------------



We can have the Result below:
-------------------------------------------
-------------------------------------------
Obs group value lookahead

1 a 1 3
2 a 3 3
3 a 3 2
4 a 2 5
5 a 5 1
6 a 1 8
7 a 8 .
8 b 1 3
9 b 3 3
10 b 3 2
11 b 2 5
12 b 5 1
13 b 1 8
14 b 8 .
-------------------------------------------
-------------------------------------------

Question:
How can I calculate the Maximum(Value), by group?

[Flip]

Max(Value) as maxval, ...
group by group

[Ken_oy]

Hi, But where can I put this statement? Thanks!

[Flip]

This should work. You may have a conflict between order by and group by using different cols.

proc sql;
create view numbered as select *,
monotonic() as obsnum from have;
create view want as select numbered.group, numbered.value, MAX(NUMBERED.VALUE) AS MAXVAL, cross.value as lookahead from numbered left join numbered as cross
on numbered.group=cross.group and numbered.obsnum+1=cross.obsnum
GROUP BY NUMBERED.GROUP
order by numbered.obsnum;
quit;

[Ken_oy]

Thank Flip!!
Thanks for your great help!!

Fixed!!

[Ken_oy]

proc sql;
create view numbered as select *,
monotonic() as obsnum from have;
create view want as select numbered.group, numbered.value, cross.value as lookahead

Max(Value) as maxval,



from numbered left join numbered as cross
on numbered.group=cross.group and numbered.obsnum+1=cross.obsnum

group by group

order by numbered.obsnum;
quit;


---------------------------------------------------

This is not working, I am sorry, I m new in SQL