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

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

JC_HK — Wed, 08 Oct 2014 23:10:46 GMT

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!

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

stat_sas — Thu, 09 Oct 2014 00:07:00 GMT

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;

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

JC_HK — Thu, 09 Oct 2014 00:12:50 GMT

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?

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

PGStats — Thu, 09 Oct 2014 00:31:45 GMT

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;

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

stat_sas — Thu, 09 Oct 2014 00:35:20 GMT

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;