topic Re: counts specific values in SAS Procedures

counts specific values

deleted_user — Mon, 22 Nov 2010 05:34:04 GMT

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

Re: counts specific values

sbb — Mon, 22 Nov 2010 12:47:09 GMT

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

Scott Barry
SBBWorks, Inc.

Re: counts specific values

SPR — Mon, 22 Nov 2010 14:30:26 GMT

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

Re: counts specific values

deleted_user — Sun, 05 Dec 2010 07:47:51 GMT

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.

Re: counts specific values

sbb — Sun, 05 Dec 2010 12:04:20 GMT

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.

Re: counts specific values

deleted_user — Sun, 05 Dec 2010 13:23:49 GMT

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

Re: counts specific values

deleted_user — Sun, 05 Dec 2010 19:31:31 GMT

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

Re: counts specific values

Cynthia_sas — Sun, 05 Dec 2010 20:02:24 GMT

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

Re: counts specific values

sbb — Sun, 05 Dec 2010 20:15:01 GMT

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.

Re: counts specific values

art297 — Sun, 05 Dec 2010 20:16:52 GMT

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

Re: counts specific values

Ksharp — Mon, 06 Dec 2010 01:36:34 GMT

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

Re: counts specific values

deleted_user — Mon, 06 Dec 2010 02:29:23 GMT

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.