turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- counts specific values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-22-2010 12:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

11-22-2010 07:47 AM

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

Scott Barry

SBBWorks, Inc.

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

11-22-2010 09:30 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-05-2010 02:47 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

12-05-2010 07:04 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-05-2010 02:31 PM

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

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

Thank you.

Message was edited by: yk2k

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

12-05-2010 03:02 PM

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

[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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

12-05-2010 03:15 PM

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.

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

12-05-2010 03:16 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

12-05-2010 08:23 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

12-05-2010 08:36 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

12-05-2010 09:29 PM

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.

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.