I want to collapse dollars by summing by a specific variable. Every column in these two observations is the exact same except the PYMT_AMT_DLR. I would like to get that load to add up together then i can can sum by TRIP later and eventually do a comparison.
LD_LEG_ID | TRIP_ID | PYMNT_AMT_DLR |
10790980 | 349314 | 144.00 |
10790981 | 349314 | -24.04 |
10790981 | 349314 | 144.00 |
Any help would be appreciated!
proc SQL is most flexible for this kind of task:
proc sql;
create table by_Ld_Leg as
select
TRIP_ID,
LD_LEG_ID,
sum(PYMNT_AMT_DLR) as sum_by_leg
from myData
group by TRIP_ID, LD_LEG_ID;
/* Later ... */
create table byTrip as
select
TRIP_ID,
sum(sum_by_leg) as sum_by_trip
from by_Ld_Leg
group by TRIP_ID;
quit;
proc SQL is most flexible for this kind of task:
proc sql;
create table by_Ld_Leg as
select
TRIP_ID,
LD_LEG_ID,
sum(PYMNT_AMT_DLR) as sum_by_leg
from myData
group by TRIP_ID, LD_LEG_ID;
/* Later ... */
create table byTrip as
select
TRIP_ID,
sum(sum_by_leg) as sum_by_trip
from by_Ld_Leg
group by TRIP_ID;
quit;
This worked exactly the way that I wanted, thank you!
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.
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.