Find maximum/minimum/average in a group with sql

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

Find a maximum/minimum/average of ordered sequencing obs within a group with SQL. For  maximum/average one just needs to replace min with max/mean function.


data test;
RETAIN G '  ';
input x1 $ x2;
IF G NE X1 THEN DO;
   G=X1;
   ID=0;
END;
ID+1;
datalines;
01 1
01 2
01 1
01 4
01 5
02 1
02 -3
02 3
02 4
02 5
;
PROC SQL;
  SELECT DISTINCT A.X1, A.ID ,A.X2, MIN(B.X2) AS MIN
  FROM TEST AS A
   JOIN TEST AS B
  ON
   A.X1=B.X1
  AND
  (A.ID-B.ID= 0 OR A.ID-B.ID= 1 OR A.ID-B.ID= 2)
  GROUP BY A.X1, A.ID
  ORDER BY A.X1, A.ID
;
QUIT;

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：Maximum minimum average Group find Group Maximum find average minimum