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
- /
- COUNTIF in SAS: counting how many cells in a row o...

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2014 07:10 PM

How can I achieve a similar countif function (excel) in SAS?

I have a dataset with several thousand columns (variables). For each observation (row), I want to count how many of those columns (variables) have a value greater than 0 (or some other threshold). How can I do this in SAS?

Similar, for each column, how can I count how many of the observations have a value that is greater than 0 (or some other threshold)?

I can do these easily in excel with countif function. But the data file is too big to be imported into excel. Thanks!

Accepted Solutions

Solution

10-08-2014
08:35 PM

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

Posted in reply to JC_HK

10-08-2014 08:35 PM

You can modify above code in array statement. Just replaced variable names a1-a7 with **_numeric_**

data want;

set have;

array a{*} **_numeric_;**

values_gt_2=0;

do _n_=1 to dim(a);

if a{_n_}>2 then values_gt_2+1;

end;

run;

All Replies

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

Posted in reply to JC_HK

10-08-2014 08:07 PM

May be something like this.

data have;

input a1-a7;

cards;

1 2 3 2 2 3 2

2 3 4 2 3 2 3

3 3 4 3 5 2 4

4 3 4 5 2 2 2

;

run;

data want;

set have;

array a{*} a1-a7;

values_gt_2=0;

do _n_=1 to dim(a);

if a{_n_}>2 then values_gt_2+1;

end;

run;

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

Posted in reply to stat_sas

10-08-2014 08:12 PM

Thanks. The problem is that I have many variables in my dataset (in the scale of thousands) and they are not named after the convention of any sequential order (like a1-a1000). Is there a easy way to rename it like the array form suggested?

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

Posted in reply to JC_HK

10-08-2014 08:31 PM

Variable lists can also refer to all the variables in a range with the format FirstVar -- LastVar. To recycle

example

data have;

input R T Y F N C P;

cards;

1 2 3 2 2 3 2

2 3 4 2 3 2 3

3 3 4 3 5 2 4

4 3 4 5 2 2 2

;

data want;

set have;

array a{*} R -- P;

values_gt_2=0;

do _n_=1 to dim(a);

if a{_n_}>2 then values_gt_2+1;

end;

run;

PG

Solution

10-08-2014
08:35 PM

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

Posted in reply to JC_HK

10-08-2014 08:35 PM

You can modify above code in array statement. Just replaced variable names a1-a7 with **_numeric_**

data want;

set have;

array a{*} **_numeric_;**

values_gt_2=0;

do _n_=1 to dim(a);

if a{_n_}>2 then values_gt_2+1;

end;

run;