proc sql distinct query

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

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;

Thanks in advance..


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

Re: proc sql distinct query

Posted in reply to VijayKumar

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

View solution in original post


All Replies
Super Contributor
Posts: 334

Re: proc sql distinct query

Posted in reply to VijayKumar

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

Thanks EJ for your answer.  This reply got me to a solution ... !!   Thank you!

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. Nocol1col2col3col4col5amount
1MNOPQ100
2MNOPQ100
3MNOPQ100
4MNOPQ105
5XYZMN200

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. Nocol1col2col3col4col5numberamount
1MNOPQ4405
2XYZMN1200

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

Posted in reply to VijayKumar

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

Posted in reply to damanaulakh88

Yes Daman, you are spot on!

Thanks for your reply and answer!

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 813 views
  • 0 likes
  • 3 in conversation