Help using Base SAS procedures

SELECT DISTINCT vs GROUP BY in proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

SELECT DISTINCT vs GROUP BY in proc sql

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;


Accepted Solutions
Solution
‎01-28-2015 07:55 PM
Contributor
Posts: 64

Re: SELECT DISTINCT vs GROUP BY in proc sql

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

View solution in original post


All Replies
Solution
‎01-28-2015 07:55 PM
Contributor
Posts: 64

Re: SELECT DISTINCT vs GROUP BY in proc sql

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 1033 views
  • 0 likes
  • 1 in conversation