topic Re: ODS Excel - proc print by in ODS and Base Reporting

ODS Excel - proc print by

Michael_Sch — Fri, 25 May 2018 09:01:36 GMT

Hi,

I want to create an Excel sheet for a lot of dealers, consisting of two spread sheets - one for each dealer called Dealership and one with graphics. I excluded the code for the graphics, because this part of the Excel is formatted the way I want it to.

On the dealership sheet, I want to see some sales figures for the whole dealer and beneath it by salesperson. The code I use is attached and the result is in "test_print_eng.xls".

The thing that I want to change is that the name of the second sales person is very close to the table of the first sales person.

Is there any way that I can use styles or any other method to get more space in between the table and the header of the second sales person? In order to make the Excel look prettier - see "test_print_eng - wished results" (either aligned left or centered, is fine for me).

I searched the web but I didn't find something on styles and the by statement in the proc print.

I know that another solution would be to generate a table for each sales person of a dealer, but I would like to use proc print and by to do the trick.

Best regards,

Michael

Re: ODS Excel - proc print by

Patrick — Sat, 26 May 2018 10:15:34 GMT

@Michael_Sch

May be consider using Proc Report instead as this procedures provides much more flexibility for tweaking your output the way you want it.

Re: ODS Excel - proc print by

Cynthia_sas — Sat, 26 May 2018 17:55:49 GMT

Hi:

I don't open Excel files. If data is presented in a DATA step program I am more inclined to try to run and fix the program. However, reading your code, and using SASHELP.SHOES, I came up with a mockup of what I think your report looks like. I used PROC REPORT for the BY group report in order to show you the difference between using a TITLE statement to get what you want and using PROC REPORT and a COMPUTE block:

The strings shown with '1)' are from a TITLE2 statement. To get extra space between the title and the previous table, you need to use TITLE1 to put a blank line before TITLE2. Instead of using the default BYLINE, I turned it off and used #BYVAL1 and #BYVAL2 in the TITLE2 statement (with j=l) to left justify the string and put the BY group values into the title area.

Compare that to the strings shown with '2)' -- those strings also identify the Region and Product, but "inside" the boundary of the table and using the same colors as the Header. The string is also left justified.

The program below uses both techniques, you'll have to decide which works best for your data and your overall program.

Hope this helps,

Cynthia

proc sort data=sashelp.shoes out=shoes;
  by region product;
  where region in ('Asia', 'Canada');
run;
   
ods excel file="c:\temp\test_report_eng.xlsx" style = normal
options(embedded_titles="yes");
 
***** First table;
ods excel options (sheet_name = 'Dealership' 
    sheet_interval = "none" tab_color='grey' 
    orientation = "landscape");
proc print data=shoes(obs=3)  noobs;
title1 justify=left bold "Salesreport";
where region = "Asia";
var region sales returns inventory;
run;
title;

**** Space in between the first and second table;
 proc odstext;
p " " /style=[color=black backgroundcolor=white];
run;
 

***** Second group of tables using BY group processing;
***** but since sheet_interval=none, all tables will be on same sheet;
***** NOTE, second sheet_name suboption will NOT be used because first;
***** sheet_name option is the name of the active sheet;
ods excel options (sheet_name = 'With PROC REPORT' 
          sheet_interval = "none" );
options nobyline;
title1 ' ';
title2 j=l bold c=black '1) Region: #byval1 Product: #byval2';

proc report data=shoes ;
where region = "Asia";
by region product;
column region product subsidiary returns inventory sales;
define region / group noprint;
define product / group noprint;
break before region / page;
compute before _page_/style=Header{just=l color=black font_weight=bold};
  brkline = catx(' ','2) Region and Product:',region, product);
  line brkline $varying50.;
endcomp;
run;

ods excel close;