CODEWITHSUNDEEP
sqlsas
Pane
Pane

Reputation: 555

SUM by Combination in SAS

I want to get from this table:

  [ProductCode] [ClientNO] [Fund]
     11            3         100
     12            4          45
     11            3          18
     12            4           5

To this one: 

[ProductCode] [ClientNO] [Fund]
     11            3        118
     12            4         50

So basically sum FUND when all the given variables match.

I'm almost there with this statement:

 Proc sql;
        create table SumByCombination as
        select *, sum(Fund) as Total
        from FundsData
        group by ProductCode,ClientNO
        ;
     quit;

But with this I get all the rows (duplicates) with a SUM column.

Edit: This is what I get.

  [ProductCode] [ClientNO] [_SUM_]
     11            3         118
     12            4          50
     11            3         118
     12            4          50

I know this should be a no-brainer but I keep getting stuck. What would be the easiest way to do this in Proc SQL ? What about other methods ?

Thanks

Upvotes: 1

Views: 1295

Answers (2)

Joe
Joe

Reputation: 63424

You're using SAS, so do it the SAS way - PROC MEANS.

proc means data=fundsdata;
var fund;
class productcode clientno;
types productcode*clientno;
output out=sumbycombination sum(fund)=fund;
run;

Upvotes: 2

Declan_K
Declan_K

Reputation: 6826

Stop using SELECT * in your queries. You should explicitly identify the columns that you want the SELECT to return.

Select * is nasty and evil and should very very rarely, if ever, be used.

Here is the SQL Fiddle, which returns your expected result

select  ProductCode
        ,ClientNO
        ,sum(Fund) as Total
from    FundsData
group by 
        ProductCode
        ,ClientNO

Upvotes: 2

Related Questions