Desktop productivity for business analysts and programmers

Sum the rows based on variable values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Sum the rows based on variable values

Hi ,

I have a dataset as below -

 

ShipmentID     ShipmentType   Customer     From          To           Priceperpackagein$    PriceofShipmentin$    

        1                         A                    X            NYC        BOS                     5                                   -

        1                         A                    X            NYC        BOS                     7                                   -

        2                         B                    Y            NYC        DEN                     -                                   30

        2                         B                    Y            NYC        DEN                     -                                   30

        3                         C                    Z            NYC        CHI                      -                                   25

        3                         C                    Z            NYC        CHI                      -                                   25

        4                         A                    W           NYC        BOS                     5                                   -

        4                         A                    W           NYC        BOS                     7                                   -

        5                         D                    X            NYC        BOS                     5                                   -

        5                         D                    X            NYC        BOS                     7                                   -

        6                         E                    Y            NYC        DEN                     5                                   -

        6                         E                    Y            NYC        DEN                     7                                   -

 

So here we see that Shipment types A, D & E are priced per package in the shipment, while types B & C are priced for the whole shipment. (to be noted : the pricing done for shipment visible in each row is a repeat value, whereas the package prices are individual and are to be summed up)

 

So I guess you see where I'm going..I want to add a column on the extreme right end named ..'TotalPricein$'

and put in a condition

for each ShipmentID, if ShipmentType  is A or D or E,

then value of TotalPricein$ = sum of Priceperpackagein$

else value of TotalPricein$ = PriceofShipmentin$

 

something along these lines..

 

the prob is that the criteria for values to be summed up are in diff rows and columns....so need help there with the logic..

perhaps in two table transformations?

Thanks!

 


Accepted Solutions
Solution
‎01-18-2018 08:29 AM
Super User
Posts: 10,860

Re: Sum the rows based on variable values

Can you post the output ?

 

data have;
input ShipmentID     ShipmentType  $ Customer   $  From    $   To $     Priceperpackagein   PriceofShipmentin;
cards;
        1                         A                    X            NYC        BOS                     5                                   .
        1                         A                    X            NYC        BOS                     7                                   .
        2                         B                    Y            NYC        DEN                     .                                   30
        2                         B                    Y            NYC        DEN                     .                                   30
        3                         C                    Z            NYC        CHI                      .                                   25
        3                         C                    Z            NYC        CHI                      .                                   25
        4                         A                    W           NYC        BOS                     5                                   .
        4                         A                    W           NYC        BOS                     7                                   .
        5                         D                    X            NYC        BOS                     5                                   .
        5                         D                    X            NYC        BOS                     7                                   .
        6                         E                    Y            NYC        DEN                     5                                   .
        6                         E                    Y            NYC        DEN                     7                                   .
;
run;

proc sql;
create table want as
 select *,coalesce(sum(Priceperpackagein),PriceofShipmentin) as total
  from have 
   group by shipmentid;
quit;

View solution in original post


All Replies
Solution
‎01-18-2018 08:29 AM
Super User
Posts: 10,860

Re: Sum the rows based on variable values

Can you post the output ?

 

data have;
input ShipmentID     ShipmentType  $ Customer   $  From    $   To $     Priceperpackagein   PriceofShipmentin;
cards;
        1                         A                    X            NYC        BOS                     5                                   .
        1                         A                    X            NYC        BOS                     7                                   .
        2                         B                    Y            NYC        DEN                     .                                   30
        2                         B                    Y            NYC        DEN                     .                                   30
        3                         C                    Z            NYC        CHI                      .                                   25
        3                         C                    Z            NYC        CHI                      .                                   25
        4                         A                    W           NYC        BOS                     5                                   .
        4                         A                    W           NYC        BOS                     7                                   .
        5                         D                    X            NYC        BOS                     5                                   .
        5                         D                    X            NYC        BOS                     7                                   .
        6                         E                    Y            NYC        DEN                     5                                   .
        6                         E                    Y            NYC        DEN                     7                                   .
;
run;

proc sql;
create table want as
 select *,coalesce(sum(Priceperpackagein),PriceofShipmentin) as total
  from have 
   group by shipmentid;
quit;
Occasional Contributor
Posts: 13

Re: Sum the rows based on variable values

Gosh, I salute you for your help! Thanks a ton

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 171 views
  • 2 likes
  • 2 in conversation