英文:
SAS related to using over and partition proc sql
问题
Submitted code
proc sql;
create table case1 as select
Name, age, height,
AVG(height) OVER (PARTITION BY age) AS avg_ht_age
from sashelp.class;
quit;
SAS Log
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
proc sql;
create table case1 as select
Name, age, height,
AVG(height) OVER (PARTITION BY age) AS avg_ht_age
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
I want to get average height for the subjects with the same age. I am using this code and it's showing an error.
POV: I know how to deal with using group by.
英文:
Submitted code
proc sql ;
create table case1 as select
Name, age, height ,
AVG(height) OVER (PARTITION BY age) AS avg_ht_age
from sashelp.class ;
quit ;
SAS Log
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
proc sql ;
create table case1 as select
Name, age, height ,
AVG(height) OVER (PARTITION BY age) AS avg_ht_age
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
I want to get average height for the subjects with same age. I am using this code and it's showing an error.
POV: I know how to deal with using group by.
答案1
得分: 3
PROC SQL 不实现 "windowing" 操作，这是 OVER 和 PARTITION BY 的一部分。它仅支持 ANSI 92 版本 SQL 的特性。
SAS 不需要这些更复杂的功能，因为（与大多数实现 SQL 的数据库系统不同）它已经有一种用于操作数据的语言。
您可以使用数据步骤或过程来进行比 SQL 无法处理的更复杂的数据操作。
英文:
PROC SQL does not implement "windowing" operations that OVER and PARTITION BY are part of. It only supports the features of ANSI 92 version of SQL.
SAS does not need these more complex features because (unlike most database systems that implement SQL) it already has a language for manipulating data.
You can use data steps or procs to do more complex data manipulations that SQL cannot handle.
答案2
得分: 0
你必须使用GROUP BY语句来对数据进行分组。
create table case1 as
select Name, age, height, AVG(height) AS Avg_ht_age
from sashelp.class
group by age
;
quit;```
<details>
<summary>英文:</summary>
You have to use group by statement to group your data.
proc sql;
create table case1 as
select Name, age, height , AVG(height) AS Avg_ht_age
from sashelp.class
group by age
;
quit;
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论