Spacing between multiple proc reports - ODS EXCEL

[pavelr]

I am using ODS Excel to print two tables on one Excel sheet. SAS adds one empty row between these tables. How can I avoid this empty row? I want two table printed one under another without any empty rows.

[maguiremq]

Can you share your code and/or output please?

Here's an answer from a prior thread. Let us know if that works.

[pavelr]

The solution with ROW_HEIGHTS does not work because I need to eliminate the empty row entirely.

Here is a sample code. The output is just two tables in Excel with an empty row between them - I want to eliminate this row. So, if the Table1 ends with row 20, I want Table2 to start with row 21.

ods excel options(

 start_at = "1,1"

 sheet_interval='none'

 sheet_name = "MySheet"

);

proc report data =&_DS1.;run;

proc report data =&_DS2. noheader;run;

[ballardw]

If that is all that your reports actually look like the question becomes how much difference is there between the two data sets? If they have the same variables append one set to the other and write one report.

If they don't have the same variables then the result would be extremely confusing as there would be no way to tell that what was measure x all of a sudden becomes measure y.

If the variables mean the same thing but have different names then rename them and append then report.

[pavelr · Accepted solution]

Thanks. No, the reason is different: rows of the same datasets must be formatted differently. 

The problem was solved using condition and CALL DEFINE in PROC REPORT.

[PaigeMiller]

---

@pavelr wrote:

Thanks. No, the reason is different: rows of the same datasets must be formatted differently. 

The problem was solved using condition and CALL DEFINE in PROC REPORT.

---

This illustrates a concern of mine about many of the questions asked here in the forum. They begin by saying "I need to have SAS do this specific task" when actually what is driving the need is something entirely different ... not skipping a row, but formatting rows differently. Had the first question you asked been stated as  "How can I format rows differently in PROC REPORT..." you would have gotten a quicker answer. But that was not mentioned and so the other answers were not useful. Please always give us an explanation of the problem and not say you want SAS to do a very specific task. (Or say both). More info here: https://www.linkedin.com/pulse/xy-problem-behind-every-what-why-brenda-lai

[pavelr]

1) Thanks for the advice. May we all ask the right questions in life 🙂

2) I do not think that the answers (that were posted before I found the solution) were not useful. And I appreciate those answers.

3) The topic of this post reflects one of the ways to solve the problem. My concern is that the implementation of this seemingly straightforward solution cannot be easily found. Using CALL DEFINE (e.g., call define(var,'format','dollar8.2');) together with conditions in PROC REPORT may be more complex especially if datasets have many columns and you need several different formats for several rows. While splitting the dataset as you need and outputting it to Excel (using ODS and different formats) part by part seems the straightforward and easy solution. Except one little thing... SAS adds this empty row between outputs, and we don't know how to remove it. I would be glad to mark (as a solution) the post with the direct answer to this post.

[ballardw]

---

@pavelr wrote:

Thanks. No, the reason is different: rows of the same datasets must be formatted differently. 

The problem was solved using condition and CALL DEFINE in PROC REPORT.

---

So now it is up to you to actually post the solution so that the next person searching this forum for an answer has an actual example they can attempt to see if your approach solves their problem.

Stating "Call Define" solved the problem does not in any way describe how it actually solved the problem.

[lisatex]

Hello,

can you post an example of solution with proc define?

I have same problem,

thank you,

Lisa

[Cynthia_sas]

Hi, Lisa:

The original poster said he could not post his code or data. So here's a simple example using SASHELP.CLASS

  Note how the call define based on NAME is changing the color of font on the whole row for Alfred to purple. But the tests for age still impact the format for the HEIGHT and WEIGHT columns. With the PROC REPORT ability to change the style and format of a single column, one of the neat things is that the last column listed on the report row can have a COMPUTE block that "touches" or applies styles and formats with CALL DEFINE to any of the other columns on the report row. There are a few more complicated rules around using COMPUTE blocks and CALL DEFINE, but you would learn about those as you read the PROC REPORT documentation and search here in the forums for previous answers to questions like this.

  Here's the full code I used to create the above output:

proc sort data=sashelp.class out=class;
by age;     
run;   
  
ods excel file='c:\temp\diff_formats.xlsx'
    options(embedded_titles='yes');
proc report data=class;
  title 'Using Call Define to format rows differently based on a condition';
  column age name sex height weight;
  define age / display;
  define name / display;
  define sex / display;
  define height / display;
  define weight/display;
  compute weight;
    ** in the compute block for weight, you can "touch" all the columns on the report row;
    if name = 'Alfred' then do;
	   call define(_row_,'style','style={font_weight=bold color=purple}');
	end;
	if age = 12 then do;
	   call define('height','format','z8.2');
	   call define(_col_,'format','z8.2');
	end;
	else if age=14 then do;
	   call define('height','format','6.2');
	   call define(_col_,'format','6.2');
	end;
  endcomp;
run;
ods excel close;
title;

   You should be able to run this code because SASHELP.CLASS should be available in every installation of SAS.

Hope this helps,

Cynthia