Help using Base SAS procedures

simplify code to create a demog table

Reply
Super Contributor
Posts: 301

simplify code to create a demog table

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

Obscol0                 placebo               drug                 total

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

Super Contributor
Posts: 301

Re: simplify code to create a demog table

options symbolgen mprint mlogic;to make a demog table

Please, remove this paragraph: "to make a demog table" if not the code doesn't work

Ask a Question
Discussion stats
  • 1 reply
  • 745 views
  • 0 likes
  • 1 in conversation