topic Re: counting numeric data proc sql excluding zero values? in SAS Programming

counting numeric data proc sql excluding zero values?

chappy — Thu, 06 Dec 2012 18:19:00 GMT

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.

Re: counting numeric data proc sql excluding zero values?

ballardw — Thu, 06 Dec 2012 18:30:01 GMT

proc sql;

select count(*) as subjects

from work.weight

where weight>0;
quit;

Re: counting numeric data proc sql excluding zero values?

chappy — Thu, 06 Dec 2012 18:51:53 GMT

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.

Re: counting numeric data proc sql excluding zero values?

Astounding — Thu, 06 Dec 2012 18:56:34 GMT

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.

Re: counting numeric data proc sql excluding zero values?

chappy — Thu, 06 Dec 2012 19:03:27 GMT

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

Re: counting numeric data proc sql excluding zero values?

Linlin — Thu, 06 Dec 2012 19:22:02 GMT

proc sql;

create table want as

select year,count(*) as subjects

from work.weight

where weight>0
group by year
quit;

Re: counting numeric data proc sql excluding zero values?

chappy — Thu, 06 Dec 2012 20:47:54 GMT

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

Re: counting numeric data proc sql excluding zero values?

Linlin — Thu, 06 Dec 2012 21:05:52 GMT

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;

Re: counting numeric data proc sql excluding zero values?

chappy — Thu, 06 Dec 2012 23:08:21 GMT

Thanks Linlin. You are the best