BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chappy
Calcite | Level 5

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
Linlin
Lapis Lazuli | Level 10

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;

View solution in original post

8 REPLIES 8
ballardw
Super User

proc sql;

select count(*) as subjects

from work.weight

where weight>0;
quit;

chappy
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

proc sql;

create table want as

select year,count(*) as subjects

from work.weight

where weight>0
group by year
quit;

chappy
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thanks Linlin. You are the best Smiley Happy

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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