## countif function

Solved
Occasional Contributor
Posts: 11

# countif function

Is there a countif function in SAS, similar to the one in Excel, that can count across columns? The data I'm working with has several columns and rows with a value of 11, 01, 10, or 00 which I need to count for each row.

SAMPLE

 SECTION 1 SECTION 2 SECTION 3 SECTION 4 SECTION 5 00 10 00 11 00 11 00 11 11 11 00 10 00 00 00 11 00 10 01 10 01 00 00 00 00 00 11 00 11 00 10 00 00 10 01 00 10 00 11 00 11 00 00 00 10

WANT

 11 01 10 00 1 0 1 3 4 0 0 1 0 0 1 4 1 1 2 1 0 1 0 4 2 0 0 3 0 1 2 2 1 0 1 3 1 0 1 3

Thanks so much!

Accepted Solutions
Solution
‎06-22-2018 04:33 PM
Posts: 1,322

## Re: countif function

Maybe the COUNT function is even more similar to Excel's COUNTIF function.

Are SECTION variables character?

``````data have;
input (section1-section5) (\$);
cards;
00 10 00 11 00
11 00 11 11 11
00 10 00 00 00
11 00 10 01 10
01 00 00 00 00
00 11 00 11 00
10 00 00 10 01
00 10 00 11 00
11 00 00 00 10
;

data want;
set have;
length s \$50;
s=catx(',', of section:);
_11=count(s, '11');
_01=count(s, '01');
_10=count(s, '10');
_00=count(s, '00');
keep _:;
run;``````

Or are they numeric?

``````data have2;
input section1-section5;
cards;
00 10 00 11 00
11 00 11 11 11
00 10 00 00 00
11 00 10 01 10
01 00 00 00 00
00 11 00 11 00
10 00 00 10 01
00 10 00 11 00
11 00 00 00 10
;

data want2;
set have2;
length s \$50;
array a section:;
do i=1 to dim(a);
s=catx(',', s, put(a[i],z2.));
end;
_11=count(s, '11');
_01=count(s, '01');
_10=count(s, '10');
_00=count(s, '00');
keep _:;
run;``````

All Replies
Super User
Posts: 13,913

Super User
Posts: 2,049

## Re: countif function

``````data have;
k=_n_;
input SECTION1	SECTION2	SECTION3	SECTION4	SECTION5;
cards;
0	10	0	11	0
11	0	11	11	11
0	10	0	0	0
11	0	10	1	10
1	0	0	0	0
0	11	0	11	0
10	0	0	10	1
0	10	0	11	0
11	0	0	0	10
;

proc transpose data=have out=_have;
by  k  ;
var section:;
run;

proc freq data=_have noprint;
by k;
tables col1/out=temp;
run;
proc transpose data=temp out=want prefix=count;
by  k  ;
var count;
id col1;
run;``````
Occasional Contributor
Posts: 11

## Re: countif function

Thanks for the response - it's much appreciated!

Solution
‎06-22-2018 04:33 PM
Posts: 1,322

## Re: countif function

Maybe the COUNT function is even more similar to Excel's COUNTIF function.

Are SECTION variables character?

``````data have;
input (section1-section5) (\$);
cards;
00 10 00 11 00
11 00 11 11 11
00 10 00 00 00
11 00 10 01 10
01 00 00 00 00
00 11 00 11 00
10 00 00 10 01
00 10 00 11 00
11 00 00 00 10
;

data want;
set have;
length s \$50;
s=catx(',', of section:);
_11=count(s, '11');
_01=count(s, '01');
_10=count(s, '10');
_00=count(s, '00');
keep _:;
run;``````

Or are they numeric?

``````data have2;
input section1-section5;
cards;
00 10 00 11 00
11 00 11 11 11
00 10 00 00 00
11 00 10 01 10
01 00 00 00 00
00 11 00 11 00
10 00 00 10 01
00 10 00 11 00
11 00 00 00 10
;

data want2;
set have2;
length s \$50;
array a section:;
do i=1 to dim(a);
s=catx(',', s, put(a[i],z2.));
end;
_11=count(s, '11');
_01=count(s, '01');
_10=count(s, '10');
_00=count(s, '00');
keep _:;
run;``````

Occasional Contributor
Posts: 11