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!
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.