SELECT DISTINCT vs GROUP BY in proc sql

JBerry — Thu, 29 Jan 2015 00:51:23 GMT

I just spent a heck of a time debugging a SAS program today, only to discover the root cause to be the difference between SELECT DISTINCT and GROUP BY inside a proc sql procedure.

From my SQL experience, these two statements are equivalent, but I am relatively new to SAS, so I did not see this coming.

Can anyone explain why? I want my sanity back. Maybe if there's a good explanation of why SAS behaves like this, I'll take back all those nasty things I uttered. :smileydevil:

data have;

input id tt$;

cards;

1 ABADSFASDFG

2 BADSOFKJPAO

3 CAFJAPOFMAV

4 AABSADSBADS

5 AADSFAWEVAA

;

proc sql;

create table want as

select distinct(substr(tt,1,1)) as first_letter

from have;

create table why as

select substr(tt,1,1) as first_letter

from have

GROUP BY 1;

quit;

Re: SELECT DISTINCT vs GROUP BY in proc sql

JBerry — Thu, 29 Jan 2015 00:55:17 GMT

Silly me didnt read my log. it appears as though SAS treats Substr as a "summary function", like a SUM or a COUNT.

topic SELECT DISTINCT vs GROUP BY in proc sql in SAS Procedures

SELECT DISTINCT vs GROUP BY in proc sql

Re: SELECT DISTINCT vs GROUP BY in proc sql