BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nishukhawale
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

2 REPLIES 2
BrunoMueller
SAS Super FREQ

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
Diamond | Level 26 RW9
Diamond | Level 26

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?

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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