proc sql

Reply
Frequent Contributor
Posts: 75

proc sql

HouseNo
PersonNoAge
10155
10225
20130
30126
30240

proc sql;

  select distinct HouseNo

    from have

      where Age gt 50

  ;

quit;

This code will allow me to find the HouseNo with PersonNo aged over 50. But instead if I want to find all the distinct HouseNo with NO members aged over 50, how should the code be?

Thanks

Frequent Contributor
Posts: 116

Re: proc sql

Hi,

If i am getting your query correctly then :

Proc sql;

select distinct houseno

from have

where Age lt 50

;

quit;

Frequent Contributor
Posts: 116

Re: proc sql

Hi,

Adding one more thing for your query.. that can contain age till 50.

Proc sql;

select distinct houseno

from have

where Age LE 50

;

quit;

Regards

Uma Shanker Saini

Respected Advisor
Posts: 3,900

Re: proc sql

your code will return HouseNo 1 as there is a person in there under the age of 50. I believe what's required is code as below:

Proc sql;

select houseno, max(age) as max_age

from have

group by houseno

having max(age) <= 50

;

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 191 views
  • 0 likes
  • 3 in conversation