SAS macro for laboratory values

Question

I'm attempting to create a SAS macro that will generate a shift table for laboratory values. I have the following data:

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

I'd like to create the following output:

                                                             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

So far I've been able to create a basic shift table, but I would like to expand this to a 'by study' macro.

Here is the output for two or more studies.

                                                             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

Here is a clean room shift report macro that can accept parameters

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

The clean approach allows the use of multilabel formats to more easily accommodate counting of combinations of baseline ranges to be shown in the report. For example:

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

The advanced Proc MEANS features COMPLETETYPES, CLASSDATA= and MLF PRELOADFMT ORDER=DATA were used to count the number of each range shift pair within the group.

SAS procedures don't have a built-in mechanism to specify a single cell should contain <n> (%) so those cell values have to be computed. I chose to do so in REPORT compute blocks instead of in a DATA step.

Developing and testing general purpose macro typically requires more data than shown in the question, so I wrote a data generator to simulate the gathering of the unicorn measure 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;