Solved
New Contributor
Posts: 3

# COUNTIF in SAS: counting how many cells in a row or column has value greater than a certain threshold

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
Posts: 1,270

## Re: COUNTIF in SAS: counting how many cells in a row or column has value greater than a certain threshold

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
Posts: 1,270

## Re: COUNTIF in SAS: counting how many cells in a row or column has value greater than a certain threshold

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;

New Contributor
Posts: 3

## Re: COUNTIF in SAS: counting how many cells in a row or column has value greater than a certain threshold

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?

Posts: 5,529

## Re: COUNTIF in SAS: counting how many cells in a row or column has value greater than a certain threshold

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
Posts: 1,270

## Re: COUNTIF in SAS: counting how many cells in a row or column has value greater than a certain threshold

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;

🔒 This topic is solved and locked.