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

Frequent Contributor
Posts: 128

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

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?
Super Contributor
Posts: 359

## Re: Any "By" or "class" statement in PROC SQL?

Max(Value) as maxval, ...
group by group
Frequent Contributor
Posts: 128

## Re: Any "By" or "class" statement in PROC SQL?

Hi, But where can I put this statement? Thanks!
Super Contributor
Posts: 359

## Re: Any "By" or "class" statement in PROC SQL?

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;
Frequent Contributor
Posts: 128

## Re: Any "By" or "class" statement in PROC SQL?

Thank Flip!!
Thanks for your great help!!

Fixed!!
Frequent Contributor
Posts: 128

## Re: Any "By" or "class" statement in PROC SQL?

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
Discussion stats
• 5 replies
• 107 views
• 0 likes
• 2 in conversation