Proc Report, ODS output with a dynamic footnotes from another table

[Defense]

Hi, SAS Friends:

I have a two dataset, TableA, TableB. TableA has four columns, and TableB only have one column (“Reasons_of_parent_are_important”). We are requested to make tableB as dynamic footnote of TableA. The expected output is show as Expect Table.rtf. Can we use proc report to do this and how to do it? If proc report is the best way, what is the alternative way?TableATableBExpectedTable

[Reeza]

1. Do you know how to do this assuming no separate tables?

2. What have you tried so far?

So to answer your questions, yes, you can do this in proc report and there’s more than one way. 

I would create a macro that created footnote statements for each line in the referenced data set and call that in the last step of the PROC REPORT. 

Or create LINE statements within a compute block. 

Proc Print would likely also work. 

---

@Defense wrote:

Hi, SAS Friends:

I have a two dataset, TableA, TableB. TableA has four columns, and TableB only have one column (“Reasons_of_parent_are_important”). We are requested to make tableB as dynamic footnote of TableA. The expected output is show as Expect Table.rtf. Can we use proc report to do this and how to do it? If proc report is the best way, what is the alternative way?TableATableBExpectedTable

---

[Defense]

Thanks for reply.

Yes, I do know how to create macro for each line as you stated, which I actually do it in another report. However, TableB is dynamic. Current, TableB only has 3 rows; however It might be 4 rows or even more than hundreds row as input raw dataset  for TableB updated.

[Defense]

I should make my question AS;

I have a two dataset, TableA, TableB. TableA has four columns, and TableB only have one column (“Reasons_of_parent_are_important”). Current the row in TableB only has 3 rows; however, the rows may changed as the input raw dataset for TableB updated.  We are requested to make tableB as DYNAMIC  footnote of TableA. The expected output is show as Expect Table.rtf. Can we use proc report to do this and how to do it? If proc report is the best way, what is the alternative way?

[Reeza]

Doesn't matter how many rows there are - well you are limited to 12. If you're going to go over that then you'll need some further processing and I would likely recommend using ODS TEXT or PROC ODSTEXT at that point. 

---

@Defense wrote:

I should make my question AS;

I have a two dataset, TableA, TableB. TableA has four columns, and TableB only have one column (“Reasons_of_parent_are_important”). Current the row in TableB only has 3 rows; however, the rows may changed as the input raw dataset for TableB updated.  We are requested to make tableB as DYNAMIC  footnote of TableA. The expected output is show as Expect Table.rtf. Can we use proc report to do this and how to do it? If proc report is the best way, what is the alternative way?

---

[Defense]

Thanks. Iet me try !!

[Defense]

Thanks !
Your code should work. How the layout of the footnotes should imbed under the tableA as a part of table. See my expected output below

[Reeza]

The results are embedded in the table in Cynthia's solution. You replied to your own post so it's not clear which solution you're referring to or what you're asking. Did you run either code and is it working? If it doesn't meet your requirements post your code and let us know what the new issues are.

[Reeza]

And? If the macro creates a FOOTNOTE statement for each line how does it matter how many lines there are? If it's not resolving all, post your code.

[andreas_lds]

---

@Reeza wrote:
And? If the macro creates a FOOTNOTE statement for each line how does it matter how many lines there are? If it's not resolving all, post your code.

---

A problem could arise if there are more than seven obs in the footnotes-dataset. But having to explain more than four things at the bottom of a table should cause the designer of the table to re-start thinking about the table-design.

[Cynthia_sas · Accepted solution]

Hi:

  Because the number of footnotes are limited, I wouldn't go that route. I'd use the LINE statement in PROC REPORT in a COMPUTE block -- that would not have a limitation on number of LINES. Well, there would be a limitation, but it would be a destination limitation. For example, if the number of dynamic lines + the number of rows in the main table exceeded the page size, as for PDF or RTF, then there would be issues with the TABLE probably needing multiple pages.

  In this program, I made a huge macro variable with ^{newline} between each line. That gives me over 32K for the big string of reasons in the table footer (not footnotes).

Cynthia

The HTML  output with 4 rows in the table footer:

The code:

data main_table;
  length reason_for_love_parent $200;
  infile datalines dlm=',' dsd;
  input reason_for_love_parent $ cnt  class1_n  class2_n;
datalines;
"They are loyal",30,24,54
"They are honest - better be careful what you a...",30,2,32
"They are very, very FUNNY",11,12,33
"They are supportive",10,10,20
"They are dedicated",2,7,9
"They are important to us", 6, 9, 15
;
run;

data dyn_table;
  length rnum $4 reason $200;
  infile datalines dlm=',' dsd;
  input rnum $ reason $;
 datalines;
1, "Teach us the most important lessons in life"
2, "Give the best advice and know the right thing to say."
3, "Given us two of the best things we could ever have: life and love."
4, "Loved us unconditionally"
;
run;

proc transpose data=dyn_table out=tr_out;
  var reason;
run;

data final_footer(keep=bigreason);
  set tr_out;
  length bigreason $32000 str $200;
  array cc $200 col:;
  do i = 1 to dim(cc);
     str=cat(trim(left(put(i,2.))),'. ',trim(cc(i)));
     bigreason = catx('^{newline 1}',trim(bigreason),str);
  end;
  call symputx('bigrs',bigreason);
run;
 
ods escapechar='^';
 proc report data=main_table;
  column reason_for_love_parent cnt  class1_n  class2_n;
  define reason_for_love_parent / order order=data;
  define cnt / 'Overall/(N)';
  define class1_n / 'Class1/(N)';
  define class2_n/ 'Class2/(N)';
  compute after / style={just=l};
 	 line "Reasons of parent are important";
	 line "&bigrs" ;
  endcomp;
run;

[Defense]

That is a great idea! works well.
Thanks

[Defense]

*Thanks. Finally

***My solution*;I got similar solution using proc sql into;

data tableB_x;

set tableb;

id=_n_;

new_rs=cat('^{newline 1}',trim(Reasons_of_parent_are_important));

if id=1 then new_rs=Reasons_of_parent_are_important;

run;

proc sql noprint;

select new_rs

into: footnote separated by ' '

from tableB_x;

quit;

%put &footnote;

ods rtf file="h:\sas\sas question\my_Expect_Table.rtf";

title1 "Expect Table : ";

ods escapechar='^';

proc report data=main_table;

column reason_for_love_parent cnt class1_n class2_n;

define reason_for_love_parent / order order=data;

define cnt / 'Overall/(N)';

define class1_n / 'Class1/(N)';

define class2_n/ 'Class2/(N)';

compute after / style={just=l};

line "Reasons of parent are important";

line "&footnote" ;

endcomp;

run;

[Defense]

libname sasq "h:\...\sas\sas question";
/* CREAT MACRO VARIABLE*/
data _null_;
set  sasq.tableb;;
call symput('footnote'||left(_n_), reasons_of_parent_are_important);
run;
%put &footnote1  &footnote3;

/*ODS OUTPUT*/
ods rtf file="h:\...\sas\sas question\Expect_Table.rtf";
title1 "Expect Table : ";

proc report data=sasq.tableA split="|" NOWD ;
column Reason_For_Love_Parent	Overall_N_	Class1_N_	Class2_N_;

define Reason_For_Love_Parent/display "Reason For Love Parent"  style=[cellwidth=2 in   just=l]; 
define Overall_N_/display "Overall(N)"  style=[cellwidth=2 in   just=c]; 
define Class1_N_/display "Class1(N)"  style=[cellwidth=2 in   just=c]; 
define Class2_N_/display "Class2(N)"  style=[cellwidth=2 in   just=c]; 

compute after /style=[just=l ];
	 line "Reasons of parent are important";
     line "&footnote1";  
	 line "&footnote2";  
     line "&footnote3";  
/*     line "&footnote4";  */
/*     line "&footnote5";  */
/*	 line "&footnote6";  */
/*     line "&footnote7";  */
/*     line "&footnote8";  */
/*     line "&footnote9";  */
/*     line "&footnote10";  */
/*	 line "&footnote11";  */
/*     line "&footnote12";  */

endcomp;
run;
ods rtf close;

Dear all:

Above is my sas code and output.  The question still remains on

TableB is dynamic. Current only have 3 rows, so it sounds works. However, when the raw data for tableB updated, it might 10s or 100s rows. Anyone could have an ideal.

Alternative, I think using Proc Sql Select into to create macro which is dynamic. but the footnotes aredisplay as long strain not listed "row by row" . Anyone has an idea to make it listed "row by raw"?

Thanks

[Reeza]

Did you run my code? As you can see from the image it works. 

If you change it to 5 lines it will still work. The 12 limit is a SAS footnote limitation. 

---

@Defense wrote:

libname sasq "h:\...\sas\sas question";
/* CREAT MACRO VARIABLE*/
data _null_;
set  sasq.tableb;;
call symput('footnote'||left(_n_), reasons_of_parent_are_important);
run;
%put &footnote1  &footnote3;

/*ODS OUTPUT*/
ods rtf file="h:\...\sas\sas question\Expect_Table.rtf";
title1 "Expect Table : ";

proc report data=sasq.tableA split="|" NOWD ;
column Reason_For_Love_Parent	Overall_N_	Class1_N_	Class2_N_;

define Reason_For_Love_Parent/display "Reason For Love Parent"  style=[cellwidth=2 in   just=l]; 
define Overall_N_/display "Overall(N)"  style=[cellwidth=2 in   just=c]; 
define Class1_N_/display "Class1(N)"  style=[cellwidth=2 in   just=c]; 
define Class2_N_/display "Class2(N)"  style=[cellwidth=2 in   just=c]; 

compute after /style=[just=l ];
	 line "Reasons of parent are important";
     line "&footnote1";  
	 line "&footnote2";  
     line "&footnote3";  
/*     line "&footnote4";  */
/*     line "&footnote5";  */
/*	 line "&footnote6";  */
/*     line "&footnote7";  */
/*     line "&footnote8";  */
/*     line "&footnote9";  */
/*     line "&footnote10";  */
/*	 line "&footnote11";  */
/*     line "&footnote12";  */

endcomp;
run;
ods rtf close;

Dear all:

Above is my sas code and output.  The question still remains on

TableB is dynamic. Current only have 3 rows, so it sounds works. However, when the raw data for tableB updated, it might 10s or 100s rows. Anyone could have an ideal.

Alternative, I think using Proc Sql Select into to create macro which is dynamic. but the footnotes aredisplay as long strain not listed "row by row" . Anyone has an idea to make it listed "row by raw"?

Thanks

---