topic Re: proc sql 'group by' query in SAS Programming

proc sql 'group by' query

jeremy4 — Mon, 29 Jul 2019 08:59:23 GMT

Hi,

Please can someone explain the importance of the 'group by' statement when using proc sql and examples of how it should be used (i.e. when it should be 'group by 1,2,3;' etc. - is it the number of variables selected i.e. if five variables are selected, it would be 'group by 1,2,3,4,5;' and excludes any sum(variable) as [new variable name] when considering how many to group by? I have an example below of my code - can it be reviewed to see if it is correct and whether any changes need to be made? Thanks!

Code:

proc sort data=test;
by ID;
run;

proc sql;
create table consumer_balance as
select ID,
sum(balance) as Total_balance,
count(*) as Volume
from test
group by 1
;
quit;

Re: proc sql 'group by' query

Kurt_Bremser — Mon, 29 Jul 2019 11:03:14 GMT

Your code is correct for producing summaries by ID.

Personally, I prefer to use the column names instead of the position numbers, as this is robust against changes in the positional lineup of result columns, and easier to read and maintain.

Re: proc sql 'group by' query

Kurt_Bremser — Mon, 29 Jul 2019 11:06:23 GMT

PS SQL does not need the extra sort, it does the necessary sorting on its own. Only under certain circumstances can the external sort help SQL in preventing bad performance or exceeding resource limits.