Need Assistance with Proc Sql code

[lisa2002]

Hello everyone,

I need some assistance with my data set... I'm still learning and I need to complete the question below for a work project.

/*what is the average BMI for each age group 2-10, 11-20, 21-30, 31-40, 41-55, and 56+ ?*/

This is what I have...I get my column but no rows, what am I missing? Please assist if possible.

proc sql;
create table BMItotals as
select mean(BMI) as BMIAVG
from sashelp.bmimen
where Age= 2-10
group by BMI, Age;
quit;

age    bmi

2        2.6

2.1     18.6

2.1      19

6         16.8

6         15.5

8         16.1

8          17.3

10       15.9

20        16.9

[ballardw]

Usually you want the variables you are grouping by on the SELECT clause as well.

However you are not specifying a range correctly.

where Age= 2-10

is going to select records where Age is the result of 2-10 or -8. Unlikely value.

where 2 le age le 10

would be more likely.

However, since the data set mentioned includes ages values such as 10.2 which group does that fall into 2-10 or 11-20? What would the rule be?

One way in SAS to create groups when using a single variable is a custom format such as below:

proc format library=work;
value agegrp
2 - < 11 = ' 2 to 10'
11 -< 21 = '11 to 20'
21 -< 31 = '21 to 30'
31 -< 41 = '31 to 40'
41 -< 56 = '41 to 55'
56 - high= '56+'
;

Proc means data=sashelp.bmimen nway;
  class age ;
  format age agegrp.;
  var bmi;
  output out=work.BMItotals mean= BMIAvg;
run;

The ranges such a 2 -<11 means is the same as the mathematical [2,11) where the 11 is not actually in the interval but the 10.9 are in the interval. Formats are useful as most of the analysis, reporting and graphics procedures will honor the groups created; you can change the groups simply by referencing a different format and not need for different variables; the logic is fairly easy to understand for specifying a range and MUCH simpler than writing a whole bunch of If/than/else or in your approach where clauses.

Proc means/ summary can be much more efficient than SQL for many summaries as you do not need to explicitly create variables and if you have multiple variables to summarize adding them to the Var statement is all that is needed. You can also get lots of different combinations of summaries with multiple variables on a class statement. You can create an output data set using the output statement or ODS output.