Solved: Re: how te create a master detail report with Proc report

Richardvan_tHoff

I want to create a master detail subdetail report within SAS BASE (proc report).

You woud have a master row with colums. The master has detail rows with different columns and detail row also subdetail rows with comments.

I don't want all the columns master,detail, subdetail on one row and master, detail, subdetail should be on the same page.

The result will be converted to pdf.

The output would look something like the below example:

Procesname Start date end date Detail info

-----------------------------------------------------------------------------------------

Walking 01-01-2024 10-04-2024 I walk very slow

Subproces start date end data Start place end place

-----------------------------------------------------------------------------------------------------

Part 1 01-1-2024 10-01-2024 New York Amsterdam

Comments

--------------------------------------------------------------------------------------------

I started very well

The walk was nice

Subproces start date end data Start place end place

-----------------------------------------------------------------------------------------------------

Part 2 10-1-2024 24-01-2024 Amsterdam Berlin

Comments

--------------------------------------------------------------------------------------------

Was perfect

swimming 10-06-2024 10-07-2024 I love swimming

detail

subdetail

Ksharp

DATA work.test ;
  LENGTH procesnaam $15.;
  LENGTH proces_start_date 8.;
  LENGTH proces_end_date 8.;
  LENGTH proces_detail $25.;
  LENGTH subproces $15.;
  LENGTH sub_start_date 8.;
  LENGTH sub_end_date 8.;
  LENGTH start_place $15.;
  LENGTH end_place $15.;
  LENGTH comments $50.;
  INFILE datalines dlm=",";
  INPUT procesnaam$ proces_start_date :ddmmyy12. proces_end_date :ddmmyy12. proces_detail$ subproces$ sub_start_date :ddmmyy12. sub_end_date :ddmmyy12. start_place$ end_place$ comments$;
  FORMAT proces_start_date ddmmyy10.;
  FORMAT proces_end_date ddmmyy10.;
  FORMAT sub_start_date ddmmyy10.;
  FORMAT sub_end_date ddmmyy10.;  
 
  datalines4;
walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,I started very well
walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,The walk was nice
walking,01-01-2024,10-04-2024,I walk very slow,part2,10-01-2024,24-01-2024,Amsterdam,Berlin,Was perfect
swimming,10-06-2024,10-07-2024,I love swimming,first swim,10-06-2024,15-06-2024,Leeuwarden,Sneek,Starting was difficult
swimming,10-06-2024,10-07-2024,I love swimming,first swim,15-06-2024,30-06-2024,Leeuwarden,Sneek,Injured myself
;;;;
RUN;
proc sort data=test out=have;
by procesnaam--end_place;
run;
data have2;
do until(last.end_place);
 set have;
 by procesnaam--end_place;
 length _comments $ 2000;
 _comments=catx('~n',_comments,comments);
end;
drop comments;
run;
data have3;
 set have2;
 by proces_detail;
 length test $ 32767;
 if first.proces_detail then do;
   id=1;test=catx('0909'x,'procesnaam','proces_start_date','proces_end_date','proces_detail')||'~n'||repeat('-',100)||'~n'||catx('090909090909090909090909'x,procesnaam,proces_start_date,proces_end_date,proces_detail);
   output;
 end;
 id=2;test=catx('0909'x,'subproces','sub_start_date','sub_end_date','start_place','end_place')||'~n'||repeat('-',100)||'~n'||catx('09090909090909090909'x,subproces,sub_start_date,sub_end_date,start_place,end_place);
 output;
 id=3;test='comments'||'~n'||repeat('-',100)||'~n'||_comments;
 output;
 keep id test;
run;
option nodate nonumber;
title;
ods escapechar='~';
ods pdf file='c:\temp\temp.pdf' ;
proc report data=have3 noheader nowd style={rules=none frame=void outputwidth=100% fontsize=16};
column  test id;
define test/display;
define id/display noprint;
compute id;
if id=2 then call define('test','style','style={leftmargin=1in}');
if id=3 then call define('test','style','style={leftmargin=2in}');
endcomp;
run;
ods pdf close;

View solution in original post

Ksharp

You'd better post a picture to demonstrate your output ,and what kind of file do you like for this report, CSV,RTF,WORD,EXCEL,PDF ?
And better post some data by sas code to let us test the code.

Richardvan_tHoff

I already stated that the result will be converted to PDF.

I will make a example in data lines and add that later on.

Richardvan_tHoff

example I used to create the report.

DATA work.test ;

LENGTH procesnaam $15.;

LENGTH proces_start_date 8.;

LENGTH proces_end_date 8.;

LENGTH proces_detail $25.;

LENGTH subproces $15.;

LENGTH sub_start_date 8.;

LENGTH sub_end_date 8.;

LENGTH start_place $15.;

LENGTH end_place $15.;

LENGTH comments $50.;

INFILE datalines dlm=",";

INPUT procesnaam$ proces_start_date :ddmmyy12. proces_end_date :ddmmyy12. proces_detail$ subproces$ sub_start_date :ddmmyy12. sub_end_date :ddmmyy12. start_place$ end_place$ comments$;

FORMAT proces_start_date ddmmyy10.;

FORMAT proces_end_date ddmmyy10.;

FORMAT sub_start_date ddmmyy10.;

FORMAT sub_end_date ddmmyy10.;

datalines4;

walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,I started very well

walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,The walk was nice

walking,01-01-2024,10-04-2024,I walk very slow,part2,10-01-2024,24-01-2024,Amsterdam,Berlin,Was perfect

swimming,10-06-2024,10-07-2024,I love swimming,first swim,10-06-2024,15-06-2024,Leeuwarden,Sneek,Starting was difficult

swimming,10-06-2024,10-07-2024,I love swimming,first swim,15-06-2024,30-06-2024,Leeuwarden,Sneek,Injured myself

;;;;

RUN;

Ksharp

DATA work.test ;
  LENGTH procesnaam $15.;
  LENGTH proces_start_date 8.;
  LENGTH proces_end_date 8.;
  LENGTH proces_detail $25.;
  LENGTH subproces $15.;
  LENGTH sub_start_date 8.;
  LENGTH sub_end_date 8.;
  LENGTH start_place $15.;
  LENGTH end_place $15.;
  LENGTH comments $50.;
  INFILE datalines dlm=",";
  INPUT procesnaam$ proces_start_date :ddmmyy12. proces_end_date :ddmmyy12. proces_detail$ subproces$ sub_start_date :ddmmyy12. sub_end_date :ddmmyy12. start_place$ end_place$ comments$;
  FORMAT proces_start_date ddmmyy10.;
  FORMAT proces_end_date ddmmyy10.;
  FORMAT sub_start_date ddmmyy10.;
  FORMAT sub_end_date ddmmyy10.;  
 
  datalines4;
walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,I started very well
walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,The walk was nice
walking,01-01-2024,10-04-2024,I walk very slow,part2,10-01-2024,24-01-2024,Amsterdam,Berlin,Was perfect
swimming,10-06-2024,10-07-2024,I love swimming,first swim,10-06-2024,15-06-2024,Leeuwarden,Sneek,Starting was difficult
swimming,10-06-2024,10-07-2024,I love swimming,first swim,15-06-2024,30-06-2024,Leeuwarden,Sneek,Injured myself
;;;;
RUN;
proc sort data=test out=have;
by procesnaam--end_place;
run;
data have2;
do until(last.end_place);
 set have;
 by procesnaam--end_place;
 length _comments $ 2000;
 _comments=catx('~n',_comments,comments);
end;
drop comments;
run;
data have3;
 set have2;
 by proces_detail;
 length test $ 32767;
 if first.proces_detail then do;
   id=1;test=catx('0909'x,'procesnaam','proces_start_date','proces_end_date','proces_detail')||'~n'||repeat('-',100)||'~n'||catx('090909090909090909090909'x,procesnaam,proces_start_date,proces_end_date,proces_detail);
   output;
 end;
 id=2;test=catx('0909'x,'subproces','sub_start_date','sub_end_date','start_place','end_place')||'~n'||repeat('-',100)||'~n'||catx('09090909090909090909'x,subproces,sub_start_date,sub_end_date,start_place,end_place);
 output;
 id=3;test='comments'||'~n'||repeat('-',100)||'~n'||_comments;
 output;
 keep id test;
run;
option nodate nonumber;
title;
ods escapechar='~';
ods pdf file='c:\temp\temp.pdf' ;
proc report data=have3 noheader nowd style={rules=none frame=void outputwidth=100% fontsize=16};
column  test id;
define test/display;
define id/display noprint;
compute id;
if id=2 then call define('test','style','style={leftmargin=1in}');
if id=3 then call define('test','style','style={leftmargin=2in}');
endcomp;
run;
ods pdf close;

Richardvan_tHoff

thank you very much for your example/solution.

Classroom Training Available!