topic Re: proc sql in SAS Procedures

proc sql

annapurna — Sat, 21 Mar 2015 15:48:42 GMT

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.

Re: proc sql

Reeza — Sat, 21 Mar 2015 16:43:24 GMT

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;

Re: proc sql

jakarman — Sat, 21 Mar 2015 17:16:41 GMT

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).