topic Re: sum up the number for a specific id in New SAS User

sum up the number for a specific id

elopomorph88 — Thu, 25 Mar 2021 15:43:13 GMT

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.

Re: sum up the number for a specific id

Reeza — Thu, 25 Mar 2021 15:45:01 GMT

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.

Re: sum up the number for a specific id

elopomorph88 — Thu, 25 Mar 2021 15:55:59 GMT

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.

Re: sum up the number for a specific id

Reeza — Thu, 25 Mar 2021 16:00:54 GMT

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.

Re: sum up the number for a specific id

PaigeMiller — Thu, 25 Mar 2021 16:07:54 GMT

@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.

Re: sum up the number for a specific id

elopomorph88 — Thu, 25 Mar 2021 16:18:19 GMT

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!

Re: sum up the number for a specific id

DaanDNR — Thu, 25 Mar 2021 16:23:58 GMT

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

Re: sum up the number for a specific id

PaigeMiller — Thu, 25 Mar 2021 16:36:05 GMT

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

id date state;

Re: sum up the number for a specific id

Reeza — Thu, 25 Mar 2021 16:52:21 GMT

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;

Re: sum up the number for a specific id

DaanDNR — Thu, 25 Mar 2021 16:56:31 GMT

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

Re: sum up the number for a specific id

Reeza — Thu, 25 Mar 2021 17:06:32 GMT

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.