CODEWITHSUNDEEP
sas
kareemhook
kareemhook

Reputation: 1

Sum up Rows with same ID and Ignore Smaller Category

I have a very large dataset with multiple columns to sum and categories, but here is an example of what i am trying to do:

I want to sum up the "dollars" column to the total Account Number level based on which line with account number has the highest number in the "people" column.  So there is just 1 line for every account number, and the State column will have whichever state has the higher people in it.

Account Number       State        Dollars          People
1                      MA             200             5
1                      NY             100             2
2                      CT             150             3
3                      OH             100             3
4                      VA             300             7
4                      FL             100             3

and it will look like this after the code:

Account Number       State        Dollars          People
1                      MA             300             7
2                      CT             150             3
3                      OH             100             3
4                      VA             400            10

I think this may be a simple fix but please help!

Thanks in advance!!

Upvotes: 0

Views: 165

Answers (1)

Anton Frolov
Anton Frolov

Reputation: 194

proc sql;
create table T2 as
select 'Account Number'n, State, sum(Dollars) as Dollars, sum(People) as People, People as People_Order
from T1
group by 'Account Number'n
order by People_Order desc
;quit;

/* keep the first row within previously performed Order By: */

proc sort data=T2 (drop=People_Order) nodupkey; by 'Account Number'n;run;

Upvotes: 1

Related Questions