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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.