proc sql

[annapurna]

my dataset has variables subject gender bmi

and a format is applied to bmi as

proc format;                                                                                                                           

value body   35-high='very obese'                                                                                                      

             30-34.9='obese'                                                                                                           

             25-29.9='over weight'                                                                                                     

             18.5-24.9='normal'                                                                                                        

             16-18.49='underweight'                                                                                                    

             low-<16='very underweight';                                                                                               

run;                                                                                                                                   

i want to generate a data set with counts for bmi using

proc sql with the following code but not able to get the desired output.

proc sql;                                                                                                                              

create table case.demo_2 as                                                                                                            

select  bmi ,count(*) as total                                                                                                         

from case.demo_1                                                                                                                       

group by bmi;                                                                                                                          

quit;

however  with proc freq am able to get the required output

proc freq data=case.demo_1;                                                                                                            

tables bmi/out=case.demo_f;                                                                                                            

run;

bmi                      count

very underweight      1

underweight             2

normal                   21

over weight             6

obese                     3

very obese             1

please help me writing a code using proc sql

thanks in advance.

[Reeza]

AFAIK proc sql doesn't recognize formats when calculating.

You'll need to convert your BMI variable using the put function:

proc format;                                                                                                                          

value body   35-high='very obese'                                                                                                     

             30-34.9='obese'                                                                                                          

             25-29.9='over weight'                                                                                                    

             18.5-24.9='normal'                                                                                                       

             16-18.49='underweight'                                                                                                   

             low-<16='very underweight';                                                                                              

run; 

data have;

do BMI=10 to 40;

output;

end;

format BMI body.;

run;

proc sql;                                                                                                                             

create table want as                                                                                                           

select  put(bmi, body.) as BMI,count(*) as total                                                                                                        

from have                                                                                                                      

group by calculated bmi;                                                                                                                         

quit;

proc print data=want;

run;

[jakarman]

Why not use proc tabulate / proc report  they are made and designed to do that type of work.

More interesting with an external DBMS system supporting SAS formats (need to publish those).