Solved
Contributor
Posts: 26

# 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: 6,785

## 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;

All Replies
Posts: 1,257

## 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: 6,785

## 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 and locked.

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

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