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!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.