topic Re: group by in SAS Programming

group by

HeatherNewton — Thu, 10 Mar 2022 07:48:18 GMT

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...

Re: group by

Kurt_Bremser — Thu, 10 Mar 2022 08:00:53 GMT

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".

Re: group by

HeatherNewton — Thu, 10 Mar 2022 08:38:43 GMT

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

Re: group by

yabwon — Thu, 10 Mar 2022 09:03:08 GMT

Hi @HeatherNewton

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

Re: group by

Kurt_Bremser — Thu, 10 Mar 2022 09:48:28 GMT

SAS PROC SQL simply tolerates the omission of the BY, but I (and probably many others) consider this bad practice.

Re: group by

ballardw — Thu, 10 Mar 2022 16:03:54 GMT

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.