Solved
Contributor
Posts: 30

# proc sql distinct query

Hi Experts,

I have a table with columns like

Col1,  col2, col3 , col4 , col5 , amount

for each distinct record for the combination of  ( Col1,  col2, col3 , col4 , col5 )   , I would like to get the count and the total sum ?

Below code is not working.... Can somebody help?  Please...

proc sql;

select distinct (Col1,  col2, col3 , col4 , col5)  ,    count(*) as number , sum(amount)   from tablename   group by  distinct (Col1,  col2, col3 , col4 , col5) ;

quit;

Accepted Solutions
Solution
‎02-20-2013 07:42 AM
Frequent Contributor
Posts: 81

## Re: proc sql distinct query

Hi Vijay,

Please find below the code and its output:-

==========================================

data new;

input col1 \$ col2 \$ col3 \$ col4 \$ col5 \$ amount;

datalines;

a b c d e 23

d e f g h 34

a b g h j 36

a b c d e 45

a b c d e 90

d e f g h 80

a s d f g 70

;

run;

proc sql;

create table summarize as select distinct col1,col2,col3,col4,col5 ,count(*) as Count , sum(amount) as Sum from new group by col1,col2,col3,col4,col5;

quit;

===========================================

Output:-

=====================================

Obs    col1    col2    col3    col4    col5    Count    Sum

1      a       b       c       d       e        3      158

2      a       b       g       h       j        1       36

3      a       s       d       f       g        1       70

4      d       e       f       g       h        2      114

=====================================

I hope this solves your query.

/Daman

All Replies
Super Contributor
Posts: 334

## Re: proc sql distinct query

I usually have better luck adding a subquery and I am assuming that the dups are existing in the amount column otherwise if its just the other columns the group by, by itself, should return distinct combinations of col1 - col5:

Proc sql;

select col1, col2, col3, col4 , col5, count(*) as number, sum(amount) as amount

from (select distinct col1, col2, col3, col4, col5, amount from tablename)

group by col1, col2, col3, col4, col5

;

quit;

An example of the data may be in order to help explain the issue you are seeing.

EJ

Contributor
Posts: 30

## Re: proc sql distinct query

After running the query I realized that I dont want the Distinct keyword...because

it will remove the duplicates and this will remove the amount also...which is not wanted....like

 Sr. No col1 col2 col3 col4 col5 amount 1 M N O P Q 100 2 M N O P Q 100 3 M N O P Q 100 4 M N O P Q 105 5 X Y Z M N 200

In above sample dataset example ,   the inner query ->  select distinct col1, col2, col3, col4, col5, amount from tablename

will select only 1 ,4  and 5 row ...    as   2 and 3rd are duplicates of 1st

But end result shoudl be like

 Sr. No col1 col2 col3 col4 col5 number amount 1 M N O P Q 4 405 2 X Y Z M N 1 200

So I think the inner query will give wrong results...  am I right?

So for my requirement, the following query should be sufficient:

Proc sql;

select col1, col2, col3, col4 , col5, count(*) as number, sum(amount) as amount

from tablename

group by col1, col2, col3, col4, col5

;

quit;

please correct if I am wrong...

I think earlier I didnt frame my question correctly. sorry about that...  and thanks for your help!

Solution
‎02-20-2013 07:42 AM
Frequent Contributor
Posts: 81

## Re: proc sql distinct query

Hi Vijay,

Please find below the code and its output:-

==========================================

data new;

input col1 \$ col2 \$ col3 \$ col4 \$ col5 \$ amount;

datalines;

a b c d e 23

d e f g h 34

a b g h j 36

a b c d e 45

a b c d e 90

d e f g h 80

a s d f g 70

;

run;

proc sql;

create table summarize as select distinct col1,col2,col3,col4,col5 ,count(*) as Count , sum(amount) as Sum from new group by col1,col2,col3,col4,col5;

quit;

===========================================

Output:-

=====================================

Obs    col1    col2    col3    col4    col5    Count    Sum

1      a       b       c       d       e        3      158

2      a       b       g       h       j        1       36

3      a       s       d       f       g        1       70

4      d       e       f       g       h        2      114

=====================================

I hope this solves your query.

/Daman

Contributor
Posts: 30

## Re: proc sql distinct query

Yes Daman, you are spot on!