BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jsphnwllms
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

5 REPLIES 5
novinosrin
Tourmaline | Level 20
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;
jsphnwllms
Fluorite | Level 6

Thanks for the response - it's much appreciated!

FreelanceReinh
Jade | Level 19

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;

 

jsphnwllms
Fluorite | Level 6

Thanks so much for the response - very helpful!

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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