PROC SQL SELECT DISTINCT vs GROUP BY

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

PROC SQL SELECT DISTINCT vs GROUP BY

Anybody have some good resources on using select distinct vs group by? I feel hesitant when using them and find myself confused as to which one to use or if there are times you need to use both.

 

Thanks!

Paul


Accepted Solutions
Solution
‎06-05-2018 03:38 PM
Respected Advisor
Posts: 4,736

Re: PROC SQL SELECT DISTINCT vs GROUP BY

[ Edited ]

@pchappus

Run below and examine the results. May be that explains things to you already a bit more.

In a nutshell: You use DISTINCT to de-duplicate rows, you use GROUP BY to aggregate values by the variables in the group by statement.

data have;
  input groupvar nvar cvar $;
  datalines;
1 10 A
1 10 A
1 20 B
2 50 B
2 40 B
;
run;

proc sql;
  select distinct groupvar, nvar, cvar
  from have
  ;
  select groupvar, sum(nvar) as sum_nvar, cvar
  from have
  group by groupvar
  ;
  select distinct groupvar, sum(nvar) as sum_nvar, cvar
  from have
  group by groupvar
  ;

  select distinct groupvar, sum(nvar) as sum_nvar, cvar
  from have
  group by groupvar
  having sum(nvar)>80
  ;

quit;

 

View solution in original post


All Replies
Solution
‎06-05-2018 03:38 PM
Respected Advisor
Posts: 4,736

Re: PROC SQL SELECT DISTINCT vs GROUP BY

[ Edited ]

@pchappus

Run below and examine the results. May be that explains things to you already a bit more.

In a nutshell: You use DISTINCT to de-duplicate rows, you use GROUP BY to aggregate values by the variables in the group by statement.

data have;
  input groupvar nvar cvar $;
  datalines;
1 10 A
1 10 A
1 20 B
2 50 B
2 40 B
;
run;

proc sql;
  select distinct groupvar, nvar, cvar
  from have
  ;
  select groupvar, sum(nvar) as sum_nvar, cvar
  from have
  group by groupvar
  ;
  select distinct groupvar, sum(nvar) as sum_nvar, cvar
  from have
  group by groupvar
  ;

  select distinct groupvar, sum(nvar) as sum_nvar, cvar
  from have
  group by groupvar
  having sum(nvar)>80
  ;

quit;

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 120 views
  • 3 likes
  • 2 in conversation