sum up the number for a specific id

[elopomorph88]

I have my data in the following format

Trip        Person        Fish

A            John            2

A            Dave            4

A            Kim              3

B            Brian            1

B            Alison           2

I don't care about the Person.  I care about the number of fish per trip.  So I am trying to sum up all the total fish for each trip.  So I am trying to get this converted into the following output: 

Trip       Fish

A           9

B           3

Any advice would be greatly appreciated. Thanks.   

[Reeza]

PROC MEANS?

proc means data=have N SUM MEAN;
class trip;
var fish;
ods output summary=want;
run;

Output data is saved in the WANT data set as well as displayed.

[elopomorph88]

That is helpful but I forgot another part of it. I am trying to create a new dataset that has the sum of the total fish for each trip and also the new dataset includes other data variables from the dataset. Here is my example with more detail:

Trip Person Fish Date State
A John 2 May 1 NC
A Dave 4 May 1 NC
A Kim 3 May 1 NC
B Brian 1 May 10 GA
B Alison 2 May 10 GA

So I want to also have the Date and State data included in the new dataset.

[Reeza]

I answered the question you asked for the output shown.... You don't actually show what you want that final result to look like similar to your first post.

[PaigeMiller]

---

@elopomorph88 wrote:
That is helpful but I forgot another part of it.

---

Can you please take some time and give us a complete and clear description of the problem and the desired output?

---

I am trying to create a new dataset that has the sum of the total fish for each trip and also the new dataset includes other data variables from the dataset. Here is my example with more detail:

Trip Person Fish Date State
A John 2 May 1 NC
A Dave 4 May 1 NC
A Kim 3 May 1 NC
B Brian 1 May 10 GA
B Alison 2 May 10 GA

So I want to also have the Date and State data included in the new dataset.

---

Are date and state always the same for each Trip? Or can they vary within trip? These are the types of things we need to know, so we don't provide an answer, and then you come back again and say you left another thing out.

[elopomorph88]

Yes, Date and State are always the same for each trip.

So the problem is that I have multiple rows for the same trip because there were multiple people on the same trip. Here is another example of my data. Hopefully it won't be clear.

Trip Person Fish Date State
A John 2 May 1 NC
A Dave 4 May 1 NC
A Kim 3 May 1 NC
B Brian 1 May 10 GA
B Alison 2 May 10 GA

So I am trying to generate a new dataset that sums up the fish for each trip and carries over the details from the other variables. So the new dataset will look like this:

Trip Fish Date State
A 9 May 1 NC
B 3 May 10 GA

Thanks for the help!

[PaigeMiller]

use the PROC MEANS code from @Reeza above, with the ID statement added

id date state;

[Reeza]

If date and state are the same across the rows and you expect them to be, either just add them to your CLASS/BY statement or include them in an ID statement. If they are not the same then you need to decide how to handle those differences explicitly.

proc means data=have N SUM MEAN NWAY;
class trip date state;
var fish;
ods output summary=want;
run;

[DaanDNR]

you might want to add a types statement to only select the desired types eg types trip*date*state;

[Reeza]

The NWAY option on the PROC MEANS statement should ensure that only the highest level is reported in the output dataset, but the TYPES statement is another way to do that.

[DaanDNR]

If your goal is to add the total number of fish caught on a trip to the detailed data of the trip you can use PROC SQL and only group on trip. In that way the summary statistics are merged to the original data.

data catch;
infile datalines;
input trip $ person $ fish;
datalines;
A John 2
A Dave 4
A Kim 3
B Brian 1
B Alison 2
;
run;

proc sql;
create table catch_stats as
select trip, person, fish, sum(fish) as total_catch
from catch
group by trip
;
quit;

will produce a dataset with the total number of fish per trip added:

trip person fish total_catch
A Kim 3 9
A John 2 9
A Dave 4 9
B Alison 2 3
B Brian 1 3