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!
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;
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;
Gosh, I salute you for your help! Thanks a ton
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.