Can we write multiple lines in an excel sheet using Proc report?

[Santt0sh]

Hi Experts,

I am trying create a proc report, where i want to insert lines in the proc report stating what the report does and also want to print some macro variables created in the program. looking at which the users will be able to find if the status of the sales target for product or regions. I know Footnotes, or Titles can be used but i have lot of products and sales figures for multiple regions or vendors.

I tried doing it using the Compute block but was able to print the values in a block.

is there a way we can write the lines to the proc report without using a compute block?

I tried searching, but was not able to find it.

Below is the sample code which i am using from one of the examples from proc report.

%let Sales = 1545865454; /*dummy sales figure which i want to print on the proc report but in a line and not a blcok.*/
ods rtf file='c:\temp\tryindent2.rtf'; 

proc report data=sashelp.class(obs=3) nowd

     style(lines)={fontfamily=Arial font_size=10pt leftmargin=.25in

                   just=l protectspecialchars=off};

title '2) use TAB with RTF Control String';

column _all_;

compute after;

   line "this is first line";

   line "\tab{this is second line}";

   line "\tab\tab{this is 3rd line}";

endcomp;
%put "sales figure for zzzzzzz region is &sales. on &sysdate9.";
%put "sales figure for yuyuiyiuyi region is &sales1. on &sysdate9.";
%put "total sales figure for NORTH region is &Tsales1. on &sysdate9.";
run;

  

ods rtf close;

Kindly suggest!

[Cynthia_sas]

Hi: The purpose of %put is to write to the SAS log file. Since PROC REPORT is writing to the RTF destination, your %PUT text will never be visible in the RTF file. So I'm not entirely sure what you want to do or where you want your sales figures to be written. The LINE statement is how you write text lines from PROC REPORT. The LINE statement ALWAYS writes output within the boundaries of the PROC REPORT table. So if your PROC REPORT output has borders on the left and right side of the report, the LINE(s) will ALWAYS be written within these boundaries. The other limitation of the LINE statement is that it can ONLY be used in a COMPUTE block that is associated with a break point for an order or group item or associated with the top or bottom of the report (COMPUTE BEFORE, COMPUTE AFTER, COMPUTE BEFORE _PAGE_, COMPUTE AFTER _PAGE_). So if you changed your %PUT to a LINE statement, it would NOT work.
The ODS TEXT statement is how you write a text string either before or after a procedure, where the line of text is OUTSIDE the boundaries of the report. So that may be a possibility.
In addition, you have 3 macro variables, &sales, &sales1 and &tsales1, but you only show a %LET that creates &sales. So the other values are not going to be found.
Cynthia

[Santt0sh]

Hi,

Thank you for your quick response!

I will try this.

[ballardw · Accepted solution]

Not sure where or what that "line" that appears on multiple physical lines represent but should be doable with ODS Escapechar functions ;

proc sort data=sashelp.class out=work.class;
   by sex age;
run;

ods escapechar='^';
proc report data=work.class ;
   columns sex age height weight;
   define sex /group;
   define age /group;
   compute after sex;
      line "This is the first line ^n  and the second line ^n after the sex value encountered";
   endcomp;
run;

Escapechar sets a special character to tell SAS ODS that certain style or functions are intended following the character. In this case the N function means "insert a line" (n2 would be two lines) (Including how to insert RAW control codes for things like RTF)

Actual data, or use a SAS supplied set, and the rules for what gets shown where. Your "%put" looks like it should be associated with a variable with the GROUP role so a "compute after" makes sense.

[Santt0sh]

Hi,

Thank you for your quick response.

The lines are displayed in the block or line 14, if i need to add 20 values then all the values will be in row 14 instead of printed in 14 - 34.

But i am trying to get my out put something like this.

[Santt0sh]

Thank you All!