topic Re: group by , select with different vars list in SAS Programming

group by , select with different vars list

Ronein — Sat, 24 Jul 2021 08:49:35 GMT

Hello

A far as I know in the sql original langauge

"Any column in SELECT clause that are not used by aggregate function (SUM, MIN, etc) needs to be listed in the GROUP BY clause."

For example:

PROC SQL; 
SELECT ACCT ,
       count(*) as nr
 FROM ACCTDAT
 GROUP BY ACCT
 HAVING NUMACCT GE 2;
quit;

I saw another code where the list of varaibles in SELECT are different then the list of varaibles in group BY

PROC SQL; 
SELECT ACCT, FNAME, LNAME, STATE, PHONE, STATUS, 
 count(*) as nr
 FROM ACCTDAT
 GROUP BY ACCT
 HAVING NUMACCT GE 2;
quit;

May anyone explain the difference?

How is it called? (Is it called re-merge??)

MAy anyone send link to read about it?

Note:

Here is the data set:

ACCT FNAME LNAME STATE PHONE STATUS
00001 KYLE RANGER FL 941-555-1234 TEST
12345 AMBER ERICKSON MN 507-012-3456 TEST
33333 ALICE ANDERSON FL 941-555-1236 CNTL
44455 HOWARD SMITH GA 706-555-0001 TEST
55666 GEORGE WILLIAMS GA 706-555-0002 CNTL
66777 NANCY BECK GA 706-555-0003 CNTL
67890 JOAN HALLEY WI 608-001-2345 TEST
76543 HEATHER SAMPSON FL 941-555-1235 TEST
77788 MARIA ALVAREZ TX 817-003-4567 TEST
88899 TINA HOLT OR 503-004-5678 CNTL
98765 KEVIN TRACEN TX 817-098-7654 TEST
99900 WALTER DENNIS MN 612-009-2345 TEST

Re: group by , select with different vars list

Kurt_Bremser — Sat, 24 Jul 2021 10:30:01 GMT

You are perfectly right, when not all variables which are not the subject of a summary function are listed in the GROUP BY, SAS will perform a remerge and write a NOTE about that to the log. This is different from most (if not all) other SQL flavors. Other SQL flavors will need a sub-select to create the same result.

Re: group by , select with different vars list

Ronein — Sat, 24 Jul 2021 10:53:26 GMT

Thanks.
I expected that in the result of this code we we will see Ids(field ACCT) that appear in more than 1 row and each id will have one row .
Actually in the rssults i see that if id has 2 rows in source data then in the resulted data will have also 2 rows...
May i ask why???

SELECT ACCT, FNAME, LNAME, STATE, PHONE, STATUS,
count(*) as nr
FROM ACCTDAT
GROUP BY ACCT
HAVING NUMACCT GE 2;
quit;

Re: group by , select with different vars list

Kurt_Bremser — Sat, 24 Jul 2021 11:01:02 GMT

Since the variable you GROUP BY (ACCT) is unique in your example data, the remerge will be noted, but have no effect.