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!
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;
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;
Thanks for the response - it's much appreciated!
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;
Thanks so much for the response - very helpful!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.