How to split cells in word report using SAS?

[lu_king]

I have a two part question.

How would I create the table below using proc report? I can't figure out how to split the cells underneath the targets!  

Second question: In my dataset I have one variable 'Target' containing both the target and the strain (Pox S1, Pox S2, etc.) would I still be able to create this table keeping it like that, or should I reshape my data set? 

Thanks for the help!

[BrunoMueller · Accepted solution]

Have a look at the sample code below. The original target variable contents is split into two variables so that we can use the nesting of the across usage.

/*
 * create dummy data
 */
data have;
  infile cards dlm=",";
  input
    week : date9.
    target : $32.
    value : 8.
  ;
  /*
   * split target into two variables
   */
  target2 = scan(target, 1, " ");
  strain = scan(target, 2, " ");
  format week date9.;
cards;
27May2024,Pox S1,19
27May2024,Pox S2,25
27May2024,Ebola A,4
27May2024,Ebola B,.
27May2024,Prion B,.
27May2024,Prion H,.
28May2024,Pox S1,10
28May2024,Pox S2,30
28May2024,Ebola A,4
28May2024,Ebola B,1
28May2024,Prion B,2
28May2024,Prion H,.
;

/*
 * create a format to print missing values
 * as requested
 */
proc format;
  value mynumber 
    . = "not detected"
    other = [8.]
  ;
run;

/*
 * proc report code
 * nocompletecols only print the actual values
 * group usage for week so that multiple rows are reduced to one
 * use of " " as label to suppress printing
 * target2 and strain used as across, the comma in the column
 * statement indicates the nesting
 */
proc report data=have nocompletecols;
  column ("week" week) target2, strain, value;
  define week / " " group;
  define target2 / " " across ;
  define strain / " " across;
  define value / " " analysis format=mynumber. center;
run;

/*
 * alternate way using proc tabulate
 */
proc tabulate data=have format=mynumber.;
class week target2 strain;
var value;

table week = " ", target2 = " " * strain = " " * value = " " * sum = " ";
run;