counts specific values

[deleted_user]

Is there any command can be used in data step which comparable to 'countif' function in excel?

[sbb]

With consideration to Excel and operating across rows, I would say the answer is no.

Scott Barry
SBBWorks, Inc.

[SPR]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.