How to count by group across rows?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

How to count by group across rows?

I'm try to count the none missing numbers by group across rows. 

 

Have

Group Q1 Q2 Q3 Q4a Q4b Q4c Q5
A 1 1 0 1   0 1
A 1 1 1 1 1 1 1
A 0   0 0     1
B 0 1   1      
B 1         0 1

 

Want

Group Count
A 17
B 6

 

Here is what I have and it doesn't seem like the grouping syntex is working:

data want;

set have;

by group;

if First.group then do;

count=0;

array var{*} Q1 Q2 Q3 Q4a Q4b Q4c Q5;

do i=1 to dim(var);

if missing(var(i))=0 then count=count+1;

end;

if Last.group;

end;

run;


Accepted Solutions
Solution
‎01-07-2016 12:17 PM
Super User
Posts: 5,353

Re: How to count by group across rows?

One way to look at the problem is this:  your array processing is taking place for only the first observation in each group.  Technically, you could remove a few pieces here and there to get the right result:

 

data want;

set have;

by group;

if First.group then count=0;

array var{*} Q1 Q2 Q3 Q4a Q4b Q4c Q5;

do i=1 to dim(var);

if missing(var(i))=0 then count=count+1;

end;

if Last.group;

run;

 
Also note that you could eliminate the looping:
 

data want;

set have;

by group;

array var{*} Q1 Q2 Q3 Q4a Q4b Q4c Q5;

if first.group then count=0;

count + n(of var{*});

if Last.group;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,116

Re: How to count by group across rows?

PROC SQL would be shorter:

data have;
input Group $ Q1 Q2 Q3 Q4a Q4b Q4c Q5;
cards;
A 1 1 0 1 . 0 1
A 1 1 1 1 1 1 1
A 0 . 0 0 . . 1
B 0 1 . 1 . . .
B 1 . . . . 0 1
;

proc sql;
create table want as
select group, sum(n(Q1,Q2,Q3,Q4a,Q4b,Q4c,Q5)) as Count
from have
group by group;
quit;
Solution
‎01-07-2016 12:17 PM
Super User
Posts: 5,353

Re: How to count by group across rows?

One way to look at the problem is this:  your array processing is taking place for only the first observation in each group.  Technically, you could remove a few pieces here and there to get the right result:

 

data want;

set have;

by group;

if First.group then count=0;

array var{*} Q1 Q2 Q3 Q4a Q4b Q4c Q5;

do i=1 to dim(var);

if missing(var(i))=0 then count=count+1;

end;

if Last.group;

run;

 
Also note that you could eliminate the looping:
 

data want;

set have;

by group;

array var{*} Q1 Q2 Q3 Q4a Q4b Q4c Q5;

if first.group then count=0;

count + n(of var{*});

if Last.group;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 298 views
  • 0 likes
  • 3 in conversation