topic Re: Working with Duplicates in SAS Procedures

Working with Duplicates

KDang — Mon, 11 Apr 2011 20:38:01 GMT

Hi,
I have a file with id's and activity:
id activity
1 2
1 5
2 1
3 2
4 1
4 4

What i want to do is create a dataset with the sum of the activity by ids (just want unique ids) so it'd look like the following:
id activity
1 7
2 1
3 2
4 5

Thanks all for your help!

Re: Working with Duplicates

AllenEBingham — Mon, 11 Apr 2011 20:43:43 GMT

Here's one possible solution:
[pre]data orig;
input id activity;
datalines;
1 2
1 5
2 1
3 2
4 1
4 4
run;

proc sort data=orig out=orig_srt;
by id;
run;

proc summary data=orig_srt;
by id;
var activity;
output out=orig_sum(drop=_type_ _freq_)
sum=;
run;

proc print data=orig_srt;
run;[/pre]

Re: Working with Duplicates

NickR — Mon, 11 Apr 2011 20:49:31 GMT

Using proc sql:

proc sql;
create table orig2 as select id,sum(activity) as activity from orig group by id order by id;
quit;

Re: Working with Duplicates

ballardw — Mon, 11 Apr 2011 21:55:07 GMT

Or modified approach using CLASS statement instead of BY. This doesn't require input data to be sorted.

proc summary data=orig_srt nway;
class id;
var activity;
output out=orig_sum(drop=_type_ _freq_) sum=;
run;