topic Re: Sum based on unique ID in SAS Programming

Sum based on unique ID

jgraupner — Tue, 20 Feb 2018 20:11:40 GMT

event_id attendance
1 17

1 17

2 3

How do I sum "attendance" only for unique even_id? Total attendance in this example should equal 20 (not 17+17+17+3+3+3).

Thanks

Re: Sum based on unique ID

PaigeMiller — Tue, 20 Feb 2018 20:17:20 GMT

UNTESTED CODE

proc sql;
      create table unique as select distinct event_id,attendance from have;
run;
proc means data=unique;
    var attendance;
run;

Re: Sum based on unique ID

Reeza — Tue, 20 Feb 2018 20:19:30 GMT

@jgraupner wrote:

event_id attendance
1 17

1 17

1 17

2 3

2 3

2 3

How do I sum "attendance" only for unique even_id? Total attendance in this example should equal 20 (not 17+17+17+3+3+3).

Thanks

Will attendance always be the same for each EVENT_ID?

If so, use PROC SORT with NODUPKEY to remove the duplicates and then sum using PROC MEANS or SUMMARY

Re: Sum based on unique ID

jgraupner — Tue, 20 Feb 2018 20:45:19 GMT

This gives me the count (N) of how many attendences there are but does not display the summary of how many people attended.

output:

N Mean StdD Min Max

940 29 30 5 100

To be clear, Summarizing all 940 (N) is not what I want. I want summary of attendance per event without duplicate. Thanks a lot!

Re: Sum based on unique ID

Astounding — Tue, 20 Feb 2018 21:19:23 GMT

Hopefully, somebody already advised you that this is not a good format to use for saving your data. Next time, listen to that person. Here's a way to "undo" the repetition:

data want;

set have;

by event_id;

if last.event_id;

total_attendance_so_far + attendance;

run;

This assumes that your data is in order by event_ID. The final total is part of the final observation.

Re: Sum based on unique ID

PaigeMiller — Tue, 20 Feb 2018 21:26:33 GMT

@jgraupner wrote:

This gives me the count (N) of how many attendences there are but does not display the summary of how many people attended.

output:

N Mean StdD Min Max

940 29 30 5 100

You can add the SUM option to the PROC MEANS statement and get the sum. Or you can multiply the mean (29) by the N (940) to get the sum.

Re: Sum based on unique ID

jgraupner — Tue, 20 Feb 2018 21:27:14 GMT

event_id attendance respondent_id demographics
1 17 1 4

1 17 2 6

1 17 3 7

2 3 1 2

2 3 2 4

2 3 3 5

Here's a clearer picture of the data I'm working with. I merged attendance data and demographic data on event_id, thus the repetition. How exactly would you recommend a 'good format' to use to save my data. Thanks.

Re: Sum based on unique ID

Astounding — Tue, 20 Feb 2018 23:28:12 GMT

I would recommend keeping them separate ... one data set for event-level data and a second data set for demographic data. There's no need to merge everything together in order to analyze the data. If you come to a point where the analysis requires that you merge them, go ahead and merge them as a temporary data set for that analysis. If that were the plan, you could have gone directly to PROC MEANS on the event-level data, with no complications.