proc sql; create table real as select apple orange std(abc) as real_std_abc, count(*) as real_cnt_abc AVG(abc) as REAL_abc from real group apple, orange;
I am studying this code and I wonder if the last line group apple, orange same as group by apple, orange?
as I dont know what gorup apple, orange would here otherwise...
Normally, I would say "try it", but this code won't run anyway because of syntax ERRORs. So fix those, then run it and see what the addition of the keyword BY changes (or does not change).
Then decide how you want consistency in your codes with regards to being "terse" or "wordy".
data lemon; input up down abc; datalines; 2 5 6 3 2 7 3 2 7 2 5 6 19 33 363 run; proc sql; create table real as select up, down, std(abc) as real_std_abc, count(*) as real_cnt_abc, AVG(abc) as REAL_abc from lemon group by up; quit;
I tried different scenario using group , or group by just keep getting same results. I still dont know what group means when it is used on its own without by... please help
At first sight I would say "the group without by will give you an error", but in case of Proc SQL it seems to be not the case.
The following test shows that Proc SQL seems to run ok wit both "group" and "group by" (in contrary to Proc FedSQL which throws an error).
Example:
1 data work.class;
2 set sashelp.class;
3 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
4
5 proc sql;
6 select sex, sum(age)
7 from work.class
8 group by sex
9 ;
10 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
11
12 proc sql;
13 select sex, sum(age)
14 from work.class
15 group sex
16 ;
17 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
18
19 proc fedsql;
20 select sex, sum(age)
21 from work.class
22 group by sex
23 ;
24 quit;
NOTE: PROCEDURE FEDSQL used (Total process time):
real time 0.09 seconds
cpu time 0.10 seconds
25
26 proc fedsql;
27 select sex, sum(age)
28 from work.class
29 group sex
30 ;
ERROR: Syntax error at or near "SEX"
31 quit;
NOTE: PROCEDURE FEDSQL used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds
I guess it may be an "undocumented feature" of Proc SQL, but if I were you I would use "group by" just for clarity of the syntax.
Bart
SAS PROC SQL simply tolerates the omission of the BY, but I (and probably many others) consider this bad practice.
I don't know where you are getting code like this to study but whatever source you have is a very bad source. If that is actually what the source has, then there are multiple errors related to not separating values on the Select clause (requires a comma between each variable / expression creating a variable.
SAS will assume the "by" when just Group is used.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.