topic Re: SUM up Vertically in Proc SQL in SAS Programming

SUM up Vertically in Proc SQL

nishukhawale — Mon, 12 Dec 2016 09:12:47 GMT

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,

Re: SUM up Vertically in Proc SQL

BrunoMueller — Mon, 12 Dec 2016 09:41:22 GMT

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.

Re: SUM up Vertically in Proc SQL

RW9 — Mon, 12 Dec 2016 11:16:11 GMT

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-data-AKA-generate/ta-p/258712

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?