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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.