Calcite | Level 5

## counting numeric data proc sql excluding zero values?

I have the following data work.weight

ID          weight

1               120

2               145

3               0

4               129

5               0

6               167

7               145

8               0

9               134

10             122

I would like to count the number of cells in "weight" greater than zero. I have tried the following codes:

proc sql;

select count(weight  > 0) as subjects

from work.weight;

quit;

The ouput count all data in weight. Not just those greater than zero. I've tried various iterations of this count equations but have not found one that will give me the correct answer (7). Can anyone help me with this?

Thanks very much.

1 ACCEPTED SOLUTION

Accepted Solutions
Lapis Lazuli | Level 10

## Re: counting numeric data proc sql excluding zero values?

Is this what you want?

data have;

input year weight;

cards;

2007          167

2007          195

2007           144

2007            0

2007          162

2008          139

2008          123

2008          155

2008           0

2008          199

;

proc sql;

create table want

as select year,sum(weight>0) as w1,count(weight) as w0,calculated w1/calculated w0 as percent

from have

group by year;

quit;

proc print;run;

8 REPLIES 8
Super User

## Re: counting numeric data proc sql excluding zero values?

proc sql;

select count(*) as subjects

from work.weight

where weight>0;
quit;

Calcite | Level 5

## Re: counting numeric data proc sql excluding zero values?

Thanks for your help. I actually have multiple columns of data as well as multiple years of data. For example,

year          weight     age     sex     dateweighed

2007          167          39          1     10OCT2007

2007          195          33          2     13OCT2007

2007          144          37          1     29SEPT2007

2007              0           33          1

2007          162          34          2     17OCT2007

2008          139          40          2     29OCT2008

2008          123          31          1      16OCT2008

2008          155          38          2      14OCT2008

2008               0          34          1

2008          199          36          1     22OCT2008

What I would like to do is get counts of the number of individuals with weights for each year. Thanks.

PROC Star

## Re: counting numeric data proc sql excluding zero values?

Could the same person have more than one weight measurement during the same year?  If so, you'll have to tell us about a variable that identifies who the person is.

Calcite | Level 5

## Re: counting numeric data proc sql excluding zero values?

No, the same person could not have more than one measurement during the same year. I've added an identifying variable for clarity.

year          weight     age     sex     dateweighed          ID

2007          167          39          1     10OCT2007          A1

2007          195          33          2     13OCT2007          A2

2007           144         37           1      29SEPT2007      A3

2007              0           33          1                                  A4

2007          162          34          2     17OCT2007          A5

2008          139          40          2     29OCT2008          B1

2008          123          31          1      16OCT2008         B2

2008          155          38          2      14OCT2008         B3

2008               0          34          1                                  B4

2008          199          36          1     22OCT2008          B5

What I would like to do is get counts of the number of individuals with weights for each year. Thanks

Lapis Lazuli | Level 10

## Re: counting numeric data proc sql excluding zero values?

proc sql;

create table want as

select year,count(*) as subjects

from work.weight

where weight>0
group by year
quit;

Calcite | Level 5

## Re: counting numeric data proc sql excluding zero values?

Thanks Linlin. That worked well. I have an additional question. What if I want to determine the percentage of subjects with weights? In this case it would be 4/5 or 80% for each year.

year          weight     age     sex     dateweighed          ID

2007          167          39          1     10OCT2007          A1

2007          195          33          2     13OCT2007          A2

2007           144         37           1      29SEPT2007      A3

2007              0           33          1                                  A4

2007          162          34          2     17OCT2007          A5

2008          139          40          2     29OCT2008          B1

2008          123          31          1      16OCT2008         B2

2008          155          38          2      14OCT2008         B3

2008               0          34          1                                  B4

2008          199          36          1     22OCT2008          B5

Lapis Lazuli | Level 10

## Re: counting numeric data proc sql excluding zero values?

Is this what you want?

data have;

input year weight;

cards;

2007          167

2007          195

2007           144

2007            0

2007          162

2008          139

2008          123

2008          155

2008           0

2008          199

;

proc sql;

create table want

as select year,sum(weight>0) as w1,count(weight) as w0,calculated w1/calculated w0 as percent

from have

group by year;

quit;

proc print;run;

Calcite | Level 5

## Re: counting numeric data proc sql excluding zero values?

Thanks Linlin. You are the best

Discussion stats
• 8 replies
• 7171 views
• 0 likes
• 4 in conversation