SUM up Vertically in Proc SQL

[nishukhawale]

Hi,

  I am trying sum up the columns using SUM function ang group by but it is not happening 

I am attaching dummy data and code I am using cuurently please suugest me solution for the same.

code:

proc sql;
create table demo.unit_capital1 as
(select t1.*,

(case when (t1.sch_class in ('539','540','541','812','813') and t1.tran_type in ('SWIN','SWINC','UNSAL','UNSALC'))
then sum(t1.trades_units)
else 0 end) as SHARES_SUBSCRIBED,
(case when (t1.sch_class in ('539','540','541','812','813') and t1.tran_type in ('SWOUT','SWOUTC','UNPUR','UNPURC'))
then sum(t1.trades_units) else 0 end) as SHARES_REDEEMED,
(case when (t1.sch_class in ('539','540','541','812','813') and t1.tran_type in ('SWIN','SWINC','UNSAL','UNSALC'))
then sum(t1.Subscription)
else 0 end) as Subscription_Money,
(case when (t1.sch_class in ('539','540','541','812','813') and t1.tran_type in ('SWOUT','SWOUTC','UNPUR','UNPURC'))
then sum(t1.Subscription) else 0 end) as Redemption_Money,
(case when (t1.sch_class in ('539','540','541','812','813') and t1.tran_type in ('SWOUT','SWOUTC','UNPUR','UNPURC'))
then sum(t1.scheme_load) else 0 end) as Load_

from demo.unit_capital t1
WHERE T1.SCH_CLASS in ('539','540','541','812','813')
and t1.upload_date >= '31DEC2015'd
group by t1.UPLOAD_DATE,t1.sch_class,t1.tran_type)
order by t1.sch_class,t1.tran_type;
quit;

Thanks,

[BrunoMueller · Accepted solution]

The CASE should just return the value from the column. Then you use the SUM function around the CASE, this should give you the correct result.

See also example below.

proc sql;
  select
    origin
    , sum( case
      when type in ("SUV") then invoice
      else 0
      end) as suv_invocie_sum
  from
    sashelp.cars
  group by
    origin
  ;
quit;

Bruno

[RW9]

Hi,

Firstly, please don't post Office files, they are a danger and I for one would not download them.  Post test in the form of a datastep in the post, help can be found here:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Secondly, your code is next to impossible to read, no good programming practices followed there.  Personally, I would think that doing this in Base SAS rather than SQL would make your code simpler and easier to read:

data want;
  set have (where=sch_class in ('539','540','541','812','813') and upload_date >= '31DEC2015'd);
  by sch_class tran_type;
  retain shares_subscribed shares_redeemed;
  if sch_class in ("SWIN","SWINC","UNSAL","UNSALC") then do;
    shares_subscribed=sum(shares_subscribed,trade_units);
    ...
  end;
  if sch_class in ("SWOUT","SWOUT","UNPUR","UNPURC") then do; 
    shares_redeeemed=sum(shares_redeemed,trade_units);
    ...
  end;
  if last.tran_type then output;
run;

Note that most of your case statements there are of no additional value to the logic, you already restrict the data to the '539','540' etc, so why add that to the case each time?