1. 

2. 

3.

 

 

思路：

1.

第一步先整个bigN,使用TRANSPOSE,有多个PERIOD时比较方便。

第二步使用OUTPUT, 主要是算各种Total.

第三步使用FREQ整两个table, 一个是目标table, 一个是shell，然后再加工下shell

第四步bigN（bigM 和 TRTN）去merge shell。

第五步再去merge 目标table，计算percent等。

第六步使用TRANSPOSE.

2.

每多一个output就是多输出一条PDV中当前记录,算各种变量总数无非就是把对应变量的所有记录都赋相同的值（所有的记录的总数）,或者把sub-group赋空值（各个group的总数）.

data ae;
set ae(where=(trta ne ' '));
sex = 'All';
output;
aedecod = '';*计算每类AEBODSYS的总和,就是计算AEBODSYS*AEDECOD,AEDECOD为空时记录条数;
output;
aedecod = 'Tot deco';*计算所有AEBODSYS的总和下的所有AEDECOD总和;
output;
aebodsys = 'Tot body';*计算所有AEBODSYS的总和;
aedecod = '';
output;
aebodsys = 'Tot body';*计算所有AEBODSYS的总和;
aedecod = 'Tot deco';*计算所有AEBODSYS的总和下的所有AEDECOD总和;
output;
run;

proc freq data = ae1 noprint;
table sex*trta*AEBODSYS*AEDECOD / out = ae2;
table sex*AEBODSYS/ out = ae3;
table sex*AEBODSYS*AEDECOD/ out = shella;
run;

 

 

 

data  ae;
set ae;
by sex trta aebodsys aedecod aesevn;
if last.aesevn;
run;

proc freq data = ae noprint;
table sex*trta*AEBODSYS*AEDECOD*aesevn / out = ae2;
table sex*AEBODSYS*AEDECOD/ out = shella(keep = sex aebodsys aedecod);
run;

data shella;
    set shella;
    do aesevn = 1,2,3;
        output;
    end;
run;

make shella, 然后merge, 下一步直接 merge bigN1 (选取bigN, trtan), 使得每个 trt 都出现相同的shella.

 

3.

*如果lev5非空,后面在添加任意一个变量即可;
data a;
    array lv{5} lev1 lev2 lev3 lev4 lev5;
    do i = 0 to 4;
        if lv[5-i] ne '' then do;
            call missing(lv[5-i+1]);*用来计算每层lev的总数;
            output;
        end;
    end;
run;

*make shell,下一步直接 merge bigN1(选取bigN,trtan), 使得每个trt都出现相同的shella;
proc freq data=a noprint;
    table sex*aedecod*lev1*lev2*lev3*lev4*lev5 / out = shella(keep = sex aedecod lev1 lev2 lev3 lev4 lev5);
run;

 

 

data adsl;
    input subjid trt01a $ sex $ saffl $ ;
    datalines;
    1 A F Y
    2 A M Y
    3 B M Y
    4 A F Y
    4 . M N
    6 A F Y
    7 B F Y
    8 A F Y
    9 B F Y
    10 A F Y
;

*trt01a的值为空的记录,transpose后对应记录也为空;
proc transpose data = adsl out = bigN(where=(col1 ne ' '));
by subjid sex saffl;
var trt01a;
run;

*sex可以按需要改,比如改成country race等,如果需要分组计算sub-group,在这处理bigN;
data bigN;
    set bigN(rename = (col1 = trta));
/*    if sex = 'Other' then do;output;end;*/
    sex = 'All';
/*    output;*/
run;

proc freq data = bigN noprint;
table sex*trta / out = bigN1;
run;

data ae;
    input subjid trta $ sex $ saffl $ aebodsys $9. aedecod $9. atc1 $ atc2 $ atc3 $ atc4 $ aesevn ;
    datalines;
    01 A F Y aebodsys1 aedecod1 atc11 atc21 atc31 atc41 2
    02 A M Y aebodsys1 aedecod2 atc12 atc22 atc32 atc42 3
    03 B M Y aebodsys1 aedecod3 atc13 atc23 atc33 atc43 1
    04 A F Y aebodsys2 aedecod2 atc11 atc21 atc31 atc41 1
    04 . M N aebodsys2 aedecod1 atc13 atc23 atc33 atc43 3
    06 A F Y aebodsys1 aedecod1 atc12 atc22 atc32 atc42 2
    07 B F Y aebodsys1 aedecod2 atc11 atc23 atc31 atc42 1
    08 A F Y aebodsys2 aedecod1 atc12 atc21 atc33 atc43 2
    09 B F Y aebodsys1 aedecod1 atc13 atc22 atc31 atc44 3
    10 A F Y aebodsys2 aedecod3 atc11 atc21 atc32 atc43 2
;

*用来输出总的aebodsys,aedecod为空是freq计数时不算;
data ae;
    set ae(where=(trta ne ' '));
    sex = 'All';
    output;
    aedecod = '';*计算每个AEBODSYS的总和;
    output;
    aebodsys = 'Tot body';*计算所有AEBODSYS的总和;
    aedecod = '';
    output;
    aebodsys = 'Tot body';*计算所有AEBODSYS的总和;
    aedecod = 'Tot deco';*计算所有AEBODSYS的总和下的所有AEDECOD总和;
    output;
run;

*在每个bodsys下(包括Tot body),输出总的aedecod;
data ae;
    set ae;
    output;
    if aedecod ne '' then do;
        aedecod = 'Tot deco';*;
        output;
    end;
run;

proc sort data = ae NODUPKEY;
by sex trta aebodsys aedecod aesevn;
run;

data  ae;
set ae;
by sex trta aebodsys aedecod aesevn;
if last.aesevn;
run;

proc freq data = ae noprint;
table sex*trta*AEBODSYS*AEDECOD*aesevn / out = ae2;
table sex*AEBODSYS*AEDECOD/ out = shella(keep = sex aebodsys aedecod);
run;

data shella;
    set shella;
    do aesevn = 1,2,3;
        output;
    end;
run;

*make sheel, 每个trta都会包含所有shella;
proc sql;
    create table shell as select distinct
    bigN1.count as bigm, bigN1.sex, bigN1.trta, aebodsys, aedecod, aesevn
    from bigN1 inner join shella on bigN1.sex = shella.sex
    order by sex,trta, aebodsys,aedecod,aesevn;
quit;

data ae3;
    merge shell(in = _in0) ae2(in = _in1 rename=(count = cnt));
    by sex trta aebodsys aedecod aesevn;
    if _in0 and not _in1 then cnt = 0;
    p = cnt / bigm;
    q = 1-p;
    z = probit(0.975);
    lowern = 2*bigm*p+z**2-1-z*SQRT(z**2-2-1/bigm+4*p*(bigm*q+1));
    uppern = 2*bigm*p+z**2+1+z*SQRT(z**2+2-1/bigm+4*p*(bigm*q-1));
    denum = 2*(bigm+z**2);
    lower = 100*lowern/denum;
    upper = 100*uppern/denum;
    if p=0 then lower=0;
    if p=1 then upper=100;
    ci = " ("|| put(lower,4.1) || ","||put(upper,5.1)||")";
    vart = compress(put(cnt,3.))||" ("||put((cnt/bigm)*100,5.1)||")";
    vart = tranwrd(vart,'(',' (');
    
    trt = catx('_',trta,put(aesevn,best.));
run;

proc sort data = ae3;
by sex aebodsys aedecod aesevn;
run;

proc transpose data = ae3 out = ae4;
by sex aebodsys aedecod;
id trt ;
var vart;
run;