sql - 用于实验室值的 SAS 宏

Question

我正在尝试创建一个 SAS 宏，它将为实验室值生成一个移位表。我有以下数据:

Study         Subject            Lab              Measure             Range            Group           Visit
Study1        001                Lab1             45                  Normal           1               Baseline
Study1        001                Lab1             50                  High             1               Visit2
Study1        001                Lab1             55                  High             1               Visit3
Study1        002                Lab1             40                  Normal           1               Baseline
Study1        002                Lab1             44                  Normal           1               Visit1
Study1        002                Lab1             45                  Normal           1               Visit2
Study1        002                Lab1             46                  Normal           1               Visit3
Study1        002                Lab1             52                  High             1               Visit4

我想创建以下输出:

                                                             Final Lab Value
 Parameter         Group              Baseline Value           Low        Normal      High      Missing

 Lab1              Study 1 Group 1    (N = 2)               
                                       LOW                      0         0            0        0
                                       NORMAL                   0         0            2 (100)  0
                                       HIGH                     0         0            0        0
                                       LOW or NORMAL (N = 2)    0         0            2 (100)  0
                                       HIGH or NORMAL (N = 2)   0         0            2 (100)  0

到目前为止，我已经能够创建一个基本的类次表，但我想将其扩展为“按研究”宏。

这是两个或多个研究的输出。

                                                             Final Lab Value
 Parameter         Group              Baseline Value           Low        Normal      High      Missing

 Lab1              Study 1 Group 1    (N = 2)               
                                       LOW                      0         0            0        0
                                       NORMAL                   0         0            2 (100)  0
                                       HIGH                     0         0            0        0
                                       LOW or NORMAL (N = 2)    0         0            2 (100)  0
                                       HIGH or NORMAL (N = 2)   0         0            2 (100)  0


                                                               Low       Normal      High      Missing

                   Study 1 Group 2    (N = 8)               
                                       LOW                      0         0            0        0
                                       NORMAL                   0         0            8 (100)  0
                                       HIGH                     0         0            0        0
                                       LOW or NORMAL (N = 8)    0         0            8 (100)  0
                                       HIGH or NORMAL (N = 8)   0         0            8 (100)  0


                                                               Low       Normal      High      Missing

                   Study 2 Group 1    (N = 8)               
                                       LOW                      0         0            0        0
                                       NORMAL                   0         0            8 (100)  0
                                       HIGH                     0         0            0        0
                                       LOW or NORMAL (N = 8)    0         0            8 (100)  0
                                       HIGH or NORMAL (N = 8)   0         0            8 (100)  0

Answer 1

这是一个可以接受参数的洁净室轮类报告宏

%shift_report (
  data=have, 
  parameter=lab, 
  groupBy=study group,  groupExpression=catx(' ','Study',study,'Group',group),
  subject=subject,
  range=snarfle_range
)

干净的方法允许使用多标签格式来更容易地适应报告中显示的基线范围组合的计数。例如:

  value $baselineRange (multilabel notsorted)
    'Low' = 'Low'
    'Normal' = 'Normal'
    'High' = 'High'
    'Low', 'Normal' = 'Low or Normal'
    'High','Normal' = 'High or Normal'
  ;

进阶 Proc MEANS功能 COMPLETETYPES , CLASSDATA=和 MLF PRELOADFMT ORDER=DATA用于计算组内每个距离偏移对的数量。

SAS 过程没有内置机制来指定单个单元格应包含 <n> (%)所以必须计算这些单元格值。我选择在 REPORT 计算块中而不是在 DATA 步骤中这样做。

开发和测试通用宏通常需要比问题中显示的更多的数据，所以我编写了一个数据生成器来模拟 unicorn 测量 snarfle 的收集:

proc format;
  value SnarfleRange
    10-30 = 'Low'
    30-55 = 'Normal'
    55-95 = 'High'
    . = 'Missing'
  ;

  value $baselineRange (multilabel notsorted)
    'Low' = 'Low'
    'Normal' = 'Normal'
    'High' = 'High'
    'Low', 'Normal' = 'Low or Normal'
    'High','Normal' = 'High or Normal'
  ;
run;

data have;
  call streaminit(123);

  do lab = 'Lab1', 'Lab2';
    do study = 1 to 3;
      do group = 1 to 3;
        do subject = 1 to 10;

          visit_top = ceil(rand('uniform', 8)); drop visit_top;
          do _n_ = 1 to visit_top;
            length visit $10;
            visit_timestamp + 1;  %* proxy for an actual timestamp;

            if _n_ = 1 
              then visit = 'Baseline'; 
              else visit = cats('Visit',_n_);

            snarfle_measure = 10 + ceil(rand('uniform',85));

            if rand('uniform') < 0.25 and _n_ = visit_top then 
              snarfle_measure = .;

            snarfle_range = put (snarfle_measure, SnarfleRange.);

            output;
          end;          
        end;
      end;
    end;
  end;
run;

%macro shift_report (data=, parameter=, groupBy=, groupExpression=, subject=, range=);
  /* presume data sorted by lab, then study, then group, then subject, then visit order
   * presume first subject visit is baseline and last subject visit is final
   *
   * presume ranges are Low, Normal, High
   * presume baseline ranges reported are Low, Normal, High, Low|Normal, High|Normal;
   */

  data firstlast_rows;
    set &data;
    by &parameter &groupBy &subject;

    * keep first and last measures, excluding subjects with only baseline;
    if (first.subject or last.subject) and (not first.subject=last.subject);

    if last.subject then visit = 'Final';

    output;

    rename &parameter = Parameter;
  run;

  * Reshape to have one row per subject;

  proc transpose data=firstlast_rows out=subject_base_final;
    by Parameter &groupBy &subject;
    var &range;
    id visit;
  run;

  * Count number of subjects in group;

  proc freq noprint data=subject_base_final;
    by Parameter &groupBy;
    table Parameter / out=group_counts;
  run;

  * Prep classData for full shift report;
  * Will allow report to show a 0 count when no subject has a ceratain shift;

  data classData;
    length baseline final $7;
    do baseline = 'Low', 'Normal', 'High';
    do final    = 'Low', 'Normal', 'High', 'Missing';
      output;
    end;
    end;
  run;

  /*
   * Note:
   *   A PreLoadFmt of a format defined with option NOTSORTED will cause
   *   order=data to follow the order of the format definition
   */

  * count the number of subjects that had which range shift from baseline;

  proc means noprint data=subject_base_final classData=classData completeTypes;
    by Parameter &groupBy;
    class baseline / MLF order=data preloadfmt ; %* Multi-label format;
    class final    /     order=data preloadfmt ;
    types baseline * final;
    format baseline $baselineRange.;
    output out=shift_freqs n=n;
  run;

  * Reshape data for Proc REPORT;

  proc transpose data=shift_freqs out=shift_table;
    by Parameter &groupBy baseline notsorted;
    var n;
    id final;
  run;    

  * Concatenate group count data with range shift count data;
  * Needed for percent computation and first row reported for group;

  data shift_table_groupn;
    set group_counts shift_table;
    by Parameter &groupBy;

    report_group = &groupExpression;  %* compute value to be shown in report for group column;

    retain group_COUNT;
    if not missing (COUNT) then group_COUNT = COUNT; %* repeat group count (# subjects), is needed for % computation;

     %* percent should only be 100 and only present for data from group_counts (freq output);
    if missing (percent)
      then row_N = sum(low,normal,high);
      else row_N = count;
  run;

  options missing = ' ';
  proc report data=shift_table_groupn;
    column 
      Parameter report_group group_count row_N 
      baseline baseline_n 
      low     low_pct
      normal  normal_pct
      high    high_pct
      missing missing_pct
    ;
    define Parameter    / order order=data;
    define report_group / order order=data;
    define group_COUNT / display noprint;
    define row_N    / display noprint;
    define baseline / display noprint;
    define low      / display noprint;
    define normal   / display noprint;
    define high     / display noprint;
    define missing  / display noprint;
    define baseline_n / 'BaseLine' computed;
    define low_pct    / 'Low'     computed;
    define normal_pct / 'Normal'  computed;
    define high_pct   / 'High'    computed;
    define missing_pct/ 'Missing' computed;

    compute after report_group;
      line ' ';
    endcomp;

    compute baseline_n / character length=25;
      baseline_n = ifc(row_N in (. 0), ' ', cats(baseline) || ' (N = ' || cats(row_N) || ')');
    endcomp;

    compute low_pct / character length=25;
      if not missing(low) then low_pct=low;
      if low > 0 then
        low_pct = cats(low) || ' (' || cats(round(100*low/group_count)) || '%)';
    endcomp;
    compute normal_pct / character length=25;
      if not missing(normal) then normal_pct=normal;
      if normal > 0 then
        normal_pct = cats(normal) || ' (' || cats(round(100*normal/group_count)) || '%)';
    endcomp;
    compute high_pct / character length=25;
      if not missing(high) then high_pct=high;
      if high > 0 then
        high_pct = cats(high) || ' (' || cats(round(100*high/group_count)) || '%)';
    endcomp;
    compute missing_pct / character length=25;
      if not missing(missing) then missing_pct=missing;
      if missing > 0 then
        missing_pct = cats(missing) || ' (' || cats(round(100*missing/group_count)) || '%)';
    endcomp;

  run;
  options missing = '.';

%mend;



* Use whatever ODS destination and output location you want;

ods html5 file='shift_report.html' path='c:\temp';


%shift_report (
  data=have, 
  parameter=lab, 
  groupBy=study group,  groupExpression=catx(' ','Study',study,'Group',group),
  subject=subject,
  range=snarfle_range
)

ods _all_ close;