Need Help with a complicated data step

[Tom]

That is a pretty simple file to read. Normal report type layout.

You need to read the first column (or two) and check whether you are reading the report heading, patient heading, sub total or total lines.  You can then read the rest of the row based on what type of row it is.

For the values that you read from the header lines save them into variables that you retain.

When you read in a detail line the OUTPUT the line.

You probably do not need to read the sub total and total lines since you should be able to calculate them from the detail lines.  But if you did then just read them and write the to a different dataset.  Looks like they are not "sub-totals" but actually means.

data want;
  infile "&path/&fname" dsd dlm='09'x truncover  ;
  length Department Person $100 ;
  length rowid 8 answer1 value1 answer2 value2
                 answer3 value3 answer4 value4 8
                 answer5 value5list $30 value5 8 answer6 $30
  ;
  length Questionnaire DateRange ReportDate $100 ;
  length x1 x2 $2000 ;
  retain Questionnaire DateRange ReportDate Department Person ;
  input x1 x2 @1 @ ;
  if _n_=1 then do;
    input / Questionnaire / DateRange / ReportDate ;
  end;
  if x1 = ' ' or x2='Answer chosen for #1' or x1='Total' then delete;
  else if x1=: 'Subtotal' then do;
    if person=' ' then Department=' ';
    else Person=' ';
  end;
  else if x2=' ' then do;
    if Department = ' ' then input Department;
    else input Person ;
  end;
  else do ;
    input rowid -- answer6;
    output ;
  end;
  drop x1 x2 ;
run;

proc print ;
  by Department Person notsorted;
  id rowid ;
  var answer1 -- answer6 ;
run;

 

[Peter_C]

Does this not need a simple proc print with a SUM statement
Or is it time for a tabulate to show just those summary totals
??