DATA Step, Macro, Functions and more

SUM up Vertically in Proc SQL

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

SUM up Vertically in Proc SQL

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,


Accepted Solutions
Solution
‎12-12-2016 06:40 AM
SAS Super FREQ
Posts: 685

Re: SUM up Vertically in Proc SQL

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

View solution in original post


All Replies
Solution
‎12-12-2016 06:40 AM
SAS Super FREQ
Posts: 685

Re: SUM up Vertically in Proc SQL

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

Super User
Super User
Posts: 7,427

Re: SUM up Vertically in Proc SQL

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?

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 244 views
  • 0 likes
  • 3 in conversation