simplify code to create a demog table

michtka — Tue, 05 Jun 2012 02:17:34 GMT

Hi guys, in order of reproducing the next table (forget Obs, only col0 placebo drug and total), I wrote the code below .

Obs

col0

placebo

drug

total

1	No of Subjects	19	21	40
2	race
3	Oriental	0	0.00%	1	4.76%	1	2.50%
4	Asian	1	5.26%	0	0.00%	1	2.50%
5	Black	7 36.84%	7 33.33%	14 35.00%
6	Caucasian	11 57.89%	13 61.90%	24 60.00%
7	sex
8	Female	9 47.37%	10 47.62%	19 47.50%
9	Male	10 52.63%	11 52.38%	21 52.50%
10	Height
11	MIN	137.5	133.3	133.3
12	MEAN	150.4	147.6	148.9
13	MAX	162.4	154.1	162.4
14	N	19	21	40
15	STD	7.09	5.40	6.34
16	Age
17	MIN	11.0	19.0	11.0
18	MEAN	25.6	30.8	28.3
19	N	19	21	40
20	MAX	50.0	56.0	56.0
21	STD	8.78	8.33	8.84
22	Weight
23	N	19	21	40
24	MIN	52.7	54.8	52.7
25	MEAN	64.3	66.8	65.6
26	STD	7.50	7.45	7.48
27	MAX	75.5	85.1	85.1

Please, could you tell me if I could simplify some step in the next code (because, I think is a bit long, and maybe I could avoid some steps)?

The code use three macros:

1) tablesc...calculate sex, and race

2) statx...calculate Height, Weight and Age.

3) transpos...Transposing the tables.

.....

procedures used:

1) proc sql:

a)in order to summarize the variables in (placebo, drug and total) avoiding proc freq. and proc transpose.

b)add extra row

2)proc means:..to calculate min, max, mean and std

3)proc transpose: to get the right structure of the final table

4) proc append: to concatenate tables.

-------------------------------------------------

Source dataset newtot:

data newtot;

length sex race trt $20;

length subno weight height age 8;

input subno weight height sex race age trt;

datalines;

1 63.7 141.85 Male Caucasian 19 Drug

2 72.6 149.22 Female Black 27 Placebo

3 60.7 147.64 Female Caucasian 26 Placebo

4 64.2 150.93 Male Caucasian 21 Placebo

5 60.6 153.56 Male Asian 41 Placebo

6 66.8 139.30 Male Caucasian 50 Placebo

7 75.5 147.93 Female Caucasian 24 Drug

8 55.8 137.51 Female Caucasian 21 Placebo

9 73.7 146.30 Male Caucasian 28 Drug

10 68.2 133.28 Male Caucasian 24 Drug

11 56.3 148.04 Female Caucasian 30 Drug

12 60.7 141.53 Male Black 29 Drug

13 63.2 147.19 Female Caucasian 21 Placebo

14 74.7 155.35 Male Black 20 Placebo

15 70.8 152.61 Female Caucasian 33 Drug

16 71.4 147.75 Male Black 37 Drug

17 60.1 162.40 Female Caucasian 28 Placebo

18 53.4 152.24 Male Caucasian 19 Placebo

19 65.4 142.22 Male Caucasian 37 Drug

20 52.7 161.64 Female Black 34 Placebo

21 57.9 153.62 Female Black 33 Drug

22 62.2 147.84 Male Black 29 Drug

23 72.9 139.20 Male Black 21 Placebo

24 67.4 143.55 Male Caucasian 20 Placebo

25 69.4 154.10 Female Caucasian 31 Drug

26 72.1 149.09 Male Caucasian 36 Drug

27 64.6 152.56 Female Black 25 Placebo

28 75.1 155.37 Female Caucasian 19 Placebo

29 63.1 153.64 Female Black 37 Drug

30 54.8 149.17 Female Black 34 Drug

31 75.5 149.39 Female Black 26 Placebo

32 72.7 149.27 Male Caucasian 29 Drug

33 68.2 149.57 Male Caucasian 11 Placebo

34 69.0 152.04 Female Caucasian 20 Drug

35 57.9 151.13 Male Caucasian 31 Placebo

36 61.5 138.67 Male Caucasian 56 Drug

37 55.5 159.60 Male Black 25 Placebo

38 71.0 148.42 Male Oriental 19 Drug

39 85.1 152.91 Female Black 37 Drug

40 58.2 148.92 Female Caucasian 24 Drug

;

run;

My Code:

---------------------------------------------------------------------------------------------------------------------------------------------

options symbolgen mprint mlogic;to make a demog table

*using proc sql to summarize sex and race according to the final table....avoiding proc freq and proc transpose;

proc sql;

create table table0n as

select 'No of Subjects' as col0, sum(trt='Drug') as Drug, sum(trt='Placebo') as Placebo, count(trt) as total

from newtot;

quit;

*creating global macrovariables for porcentages;

data null;

set table0n;

call symput('drugx',drug);

call symput('placebox',placebo);

call symput('totalx',total);

run;

%put &drugx;

%put &placebox;

%put &totalx;

*convert placebo, drug and total to character variables;

data table0c (rename=(drugc=drug placeboc=placebo totalc=total)drop=drug placebo total);

set table0n;

drugc=put(drug,best12.);

placeboc=put(placebo,best12.);

totalc=put(total,best12.);

run;

*macro for generate tablecx, x=1 sex, x=2 race ;

%macro tablesc(num=,var=);

proc sql;

create table table&num as

select &var as col0, sum(trt='Drug') as Drug, sum(trt='Placebo') as Placebo, count(trt) as total

from newtot

group by &var

order by &var descending;

quit;

*change numeric variables to character variables;

data tablec&num (rename=(drugc=drug placeboc=placebo totalc=total)drop=drug placebo total);

length drugc $40 placeboc $40 totalc $40;

set table&num;

drugc=put(drug,best12.)||' ' || put(drug/&drugx,percent9.2);

placeboc=put(placebo,best12.)||' ' || put(placebo/&placebox,percent9.2);

totalc=put(total,best12.)||' ' || put(total/&totalx,percent9.2);

run;

*Adding new record (Header of table);

proc sql;

insert into tablec&num

(Drug,Placebo, total, Col0)

values

(' ',' ', ' ', "&var");

quit;

proc sort data=tablec&num; by total; run; *Fixing the Header at beginning of the table;

*accumulating tables sex and race to get the final table;

proc append base=name0ok data=tablec&num; run;

%mend;

*executing macro to get the table(sex,race) = tablectotal;

%tablesc(num=1,var=race);

%tablesc(num=2,var=sex);

*macro to generate stat table tablestatx, x=1 height, x=2 age, x=3 weight;

%macro statx(var=,out=);

*sorting the source table by trt before use proc means;

proc sort data=newtot out=newtots;

by trt;

run;

*proc means by trt;

proc means data=newtots noprint;

var &var;

output out=newstat (rename=(_stat_=col0) drop=_type_ _freq_);

by trt;

run;

*proc means (total);

proc means data=newtots noprint;

var &var;

output out=newstat2(rename=(_stat_=col0) drop=_type_ _freq_);

run;

*concatenaiting the tables (total+drug);

data totnewstat;

set newstat newstat2;

if trt= ' ' then trt='Total';

run;

*sorting these tables to use proc transpose;

proc sort data=totnewstat out=totnewstat&out (rename=(&var=col1));

by col0;

run;

%mend;

%statx(var=height,out=h);

%statx(var=weight,out=w);

%statx(var=age,out=a);

*In this macro, we will traspose the (drug+total) statistical tables;

%macro transpos(in=,name=,var=);

*formating the these tables;

data newstatformat (drop=col1);

set totnewstat&in;

if col0 in ('MEAN','MAX','MIN') then value=strip(put(col1,6.1));

else if col0 in ('STD') then value=strip(put(col1,6.2));

else value=strip(put(col1,6.0));

run;

*transposing these tables;

proc transpose data=newstatformat out=&name (drop=_name_);

by col0;

id trt;

var value;

run;

*Adding new record (Header of table);

proc sql;

insert into &name

(Drug,Placebo,Total,col0)

values

(' ',' ', ' ', "&var");

quit;

proc sort data=&name; by total; run; *Fixing the Header at beginning of the table;

*accumulating tables sex and race to get the final table;

proc append base=nameok data=&name; run;

%mend;

%transpos(in=h,name=newhstat,var=Height);

%transpos(in=a,name=newastat,var=Age);

%transpos(in=w,name=newwstat,var=Weight);

data table;

length col0 $40 drug $40 placebo $40 total $40;

set table0c Name0ok Nameok;

run;

proc sql;

create table tabledemog as

select col0 label '------', placebo, drug, total

from table;

quit;

proc print data=tabledemog; run;

-------------------------------------------------------------------------------------

Thanks.

topic Re: simplify code to create a demog table in SAS Procedures

simplify code to create a demog table

Re: simplify code to create a demog table