Desktop productivity for business analysts and programmers

countif function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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
Trusted Advisor
Posts: 1,322

Re: countif function

Posted in reply to jsphnwllms

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;

 

View solution in original post


All Replies
Super User
Posts: 2,049

Re: countif function

Posted in reply to jsphnwllms
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

Posted in reply to novinosrin

Thanks for the response - it's much appreciated!

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

Re: countif function

Posted in reply to jsphnwllms

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

Re: countif function

Posted in reply to FreelanceReinhard

Thanks so much for the response - very helpful!

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 170 views
  • 0 likes
  • 4 in conversation