counting numeric data proc sql excluding zero values?

[chappy]

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.

[ballardw]

proc sql;

select count(*) as subjects

from work.weight

where weight>0;
quit;

[chappy]

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.

[Astounding]

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.

[chappy]

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

[Linlin]

proc sql;

create table want as

select year,count(*) as subjects

from work.weight

where weight>0
group by year
quit;

[chappy]

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

[Linlin · Accepted solution]

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;

[chappy]

Thanks Linlin. You are the best