topic Re: countif function in SAS Enterprise Guide

countif function

jsphnwllms — Fri, 22 Jun 2018 16:15:27 GMT

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!

Re: countif function

ballardw — Fri, 22 Jun 2018 16:21:07 GMT

The forum lets you search for topics:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Equivelant-of-Excel-s-COUNTIF-in-Advanced-Expression-Editor/m-p/165222/highlight/true#M12723

https://communities.sas.com/t5/SAS-Enterprise-Guide/Countif-equivalent/m-p/334452/highlight/true#M22239

Re: countif function

novinosrin — Fri, 22 Jun 2018 16:42:06 GMT

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;

Re: countif function

FreelanceReinh — Fri, 22 Jun 2018 20:30:08 GMT

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;

Re: countif function

jsphnwllms — Fri, 22 Jun 2018 20:35:15 GMT

Thanks so much for the response - very helpful!

Re: countif function

jsphnwllms — Fri, 22 Jun 2018 20:36:41 GMT

Thanks for the response - it's much appreciated!