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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.