Help using Base SAS procedures

counts specific values

Reply
N/A
Posts: 0

counts specific values

Is there any command can be used in data step which comparable to 'countif' function in excel?
Super Contributor
Super Contributor
Posts: 3,174

Re: counts specific values

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

Scott Barry
SBBWorks, Inc.
Super Contributor
Super Contributor
Posts: 365

Re: counts specific values

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: counts specific values

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: counts specific values

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: counts specific values

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
SAS Super FREQ
Posts: 8,868

Re: counts specific values

Posted in reply to deleted_user
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
Super Contributor
Super Contributor
Posts: 3,174

Re: counts specific values

Posted in reply to Cynthia_sas
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.
PROC Star
Posts: 7,492

Re: counts specific values

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: counts specific values

Posted in reply to 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
Super User
Posts: 10,046

Re: counts specific values

Posted in reply to deleted_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
N/A
Posts: 0

Re: counts specific values

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.
Ask a Question
Discussion stats
  • 11 replies
  • 2987 views
  • 0 likes
  • 6 in conversation