topic Any "By" or "class" statement in PROC SQL? in SAS Procedures

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

Ken_oy — Wed, 29 Jul 2009 17:43:26 GMT

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?

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

Flip — Wed, 29 Jul 2009 17:56:28 GMT

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

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

Ken_oy — Wed, 29 Jul 2009 18:00:44 GMT

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

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

Ken_oy — Wed, 29 Jul 2009 18:02:43 GMT

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

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

Flip — Wed, 29 Jul 2009 18:04:49 GMT

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;

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

Ken_oy — Wed, 29 Jul 2009 18:09:05 GMT

Thank Flip!!
Thanks for your great help!!

Fixed!!