Count over Time; 1 variable

[agambacc]

Hello!

I am trying to count multiple committees served over 3 years on one row.

Currently my Proc Report is repeating the committee on to separate rows by year thus the count is also on the same row. 

Current Code:

proc report data=committee_sort;
column division sort dname Name CommitteeName year(count),sum;
define division/group order=data;
define sort/noprint order=data;
define dname/group;
define Name/group;
define CommitteeName/group;
define year/across;
define count/analysis;
rbreak after/summarize;
run;

Sample Data:

Here is one row right now..

When a person serves on the same committee 2 years in a row it shows as below.

Division	dname	Name	CommitteeName	2014	2015	2016
H	AM	Student 3	Advisory Committee	1	.	.
				.	.	1

Here is how I would like to have it display and count.

I want one row when a committee is served on for 2 or 3 years and then provide a count across years column.

Division	dname	Name	CommitteeName	2014	2015	2016	Total Across Years
H	AM	Student 3	Advisory Committee	1	.	1	2

[RW9]

Proc report is not the place to be doing data manipulation.  Do that in a datastep.  Next up post test data in the form of a datastep, just showing some text tells us very little.  Does the second row in your data not have the id variables?  If so how do you know both those rows belong to the same ids?

[agambacc]

My apologies. It may be the case that proc report is not the place for this however I was unsure of how to handle it in the report or datastep. What I posted was the output which is not in the format that I prefer. 

data test_r (keep=id year department division committeename name count);
set test;
count=1;
run;

ID	year	Department	Division	CommitteeName	Name	Count
1	2014	MB	SS	University Budget Committee	Person 1	1
1	2015	MB	SS	University Budget Committee	Person 1	1
2	2015	MB	SS	Ad Hoc Committee on Faculty Standards of Conduct	Person 2	1
2	2016	MB	SS	Ad Hoc Committee on Faculty Standards of Conduct	Person 2	1
3	2014	MB	SS	Ad Hoc University Committee on Entrepreneurship	Person 3	1
3	2015	MB	SS	Cooperative Research Committee	Person 3	1
3	2016	MB	SS	Radiation Safety Committee	Person 3	1
4	2014	MB	SS	Scholar Awards Committee	Person 4	1
4	2015	MB	SS	Scholar Awards Committee	Person 4	1
4	2016	MB	SS	Scholar Awards Committee	Person 4	1
6	2014	MB	SS	Executive Committee	Person 5	1
6	2016	MB	SS	Biological Sciences Advisory Committee	Person 5	1

[RW9 · Accepted solution]

Data in the form of a dataset please.  As such this is untested:

proc transpose data=have out=want prefix=year;
  by comitee_name;
  var count;
  id year;
  idlabel year;
run;

data want;
  set want;
  total=sum(of year:);
run;

Step 1 transposes the data so years go across, then the datastep simply sums across the row.