BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Is there any command can be used in data step which comparable to 'countif' function in excel?
11 REPLIES 11
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
With consideration to Excel and operating across rows, I would say the answer is no.

Scott Barry
SBBWorks, Inc.
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello Yk2k,

You can use proc SQL/data step with where/if statement depending on your needs. If you provide an example then I could be more specific.

Sincerely,
SPR
deleted_user
Not applicable
Hello SPR,

I have tried as follows;

data a;
input (a1-a8) (1.);
array x {*} a1-a8;
do i=1 to dim{x};
if x{i}=1 then num+1;
end;
drop i;
datalines;
11111111
10101010
11100110
10101101
10110111
00010110
00010100
01011101
;
run;

but it still has a problem that counts cumulatively.
can you give me a hand to solve this problem?
Thank you.

Youngdeok Kim.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Given the input data example you have now provided, please illustrate in a post-reply exactly what "output" result you wish to see in a SAS file.

As I mentioned in a previous reply, COUNTIF in Excel operates across rows, possibly even within a column/cell range, which is your basic challenge -- not a difficult one if you only want to operate on column values within a given row.

So, if you can illustrate with an output results example, then it may be possible for others to help.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
What I expect is to count specific value across rows.

In this data example, I want to count '1' within each row.

Thank you.

Message was edited by: yk2k
Cynthia_sas
SAS Super FREQ
If ALL you want to do is summarize A1-A8 on every row, then you do not need to use an ARRAY:
[pre]
data a;
input (a1-a8) (1.);

num = sum(of a1-a8);
return;
datalines;
11111111
10101010
11100110
10101101
10110111
00010110
00010100
01011101
;
run;

proc print data=a;
sum a1-a8 num;
run;
[/pre]

In the above program, the SUM function is used to summarize A1-A8 on every observation/row and then in the PROC PRINT step, a summary line is displayed which summarizes (in a grand total) the values for A1-A8 and NUM.

Since your variables follow a regular numbered pattern, you can use the OF syntax in the SUM function. If you had differently named variables, then your SUM function would need to be invoked differently:
[pre]
newvar = sum(a1, wombat, b4, x, koala, somevar, other);
[/pre]

cynthia
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
The OP has not given an illustration of what is expected on the output side -- again, an illustration of INPUT and OUTPUT (visual perspective) will help....and consider that it may even help the OP determine what is being conveyed as compared to the true requirements. At this point, it is still unclear what the "result" should look like -- please share a data output example, not words.

Scott Barry
SBBWorks, Inc.
art297
Opal | Level 21
Cynthia already answered your question of how you can do it, but she didn't address your question of why your attempt counts cumulatively.

When you use the construct num+1, SAS automatically retains the value across rows. If you simply added a statement:
num=0;

before your do loop, it wouldn't end up counting cumulatively.

HTH,
Art
deleted_user
Not applicable
Hello,

If you want to count on columns then asimple solution may be:

data a;
input (a1-a8) (1.);
array x {*} a1-a8;
array count {*} count1-count8;
do i=1 to dim(x);
if x{i}=1 then count{i}+1;
end;
drop i;
datalines;
11111111
10101010
11100110
10101101
10110111
00010110
00010100
01011101
;
run;

Regards,
Marius
Ksharp
Super User
Hi.
I think you should use SAS's sum statement.


[pre]
data a;
input (a1-a8) (1.);
total_sum+ sum(a1=1,a2=1,a3=1,a4=1,a5=1,a6=1,a7=1,a8=1);
datalines;
11111111
10101010
11100110
10101101
10110111
00010110
00010100
01011101
;
run;
proc print noobs;run;
[/pre]



Ksharp
deleted_user
Not applicable
I appreciate all of you who have replied my question. My primary question was to see if any option in data step which is comparable to 'countif' is avaiable in SAS and sbb already gave a straight answer. but, I apologize if my question was too ambiguous.
Putting 'num=0' prior to 'do loop' solves my next inquiry when primary interest is to count specific value within each row.
Thank you.

Youngdeok.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 4972 views
  • 0 likes
  • 6 in conversation