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.
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;
proc sql;
select count(*) as subjects
from work.weight
where weight>0;
quit;
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.
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.
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
proc sql;
create table want as
select year,count(*) as subjects
from work.weight
where weight>0
group by year
quit;
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
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;
Thanks Linlin. You are the best
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: