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# 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# 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# 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∈ 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. V
... View more
