Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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
Trusted Advisor
Posts: 1,204

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

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?

Respected Advisor
Posts: 4,659

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
Trusted Advisor
Posts: 1,204

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 7792 views
  • 6 likes
  • 3 in conversation