The SAS Output Delivery System and reporting techniques

ExcelXP - 2 procs on the same tab when both us by vars?

Reply
Contributor
Posts: 53

ExcelXP - 2 procs on the same tab when both us by vars?

Hi guys, I'm trying to create an Excel workbook with the ExcelXP tagset. I have 2 reports, both using a by variable, and I would like the final workbook to have a tab for every occurance of the by variable with the output of both reports for that by variable on it.

The below code creates a tab for each unique value of "name" but each proc produces it's own set of tabs. I've made this work with the PDF destination using the "ods pdf startpage=no" option but I can't seem to get the behavior to work for ExcelXP. Maybe a combo of "sheet_interval" options? Any help would be appreciated.

ods tagsets.ExcelXP path="&out_path" file="&out_file"
options(sheet_interval='bygroup');

proc print data=test;
by name;

proc print data=test2;
by name;

ods _all_close;
Valued Guide
Posts: 2,175

Re: ExcelXP - 2 procs on the same tab when both us by vars?

I think this is the purpose of the ODS Document destination. and PROC DOCUMENT
SAS Super FREQ
Posts: 8,743

Re: ExcelXP - 2 procs on the same tab when both us by vars?

Hi:
I think the issue is that with sheet_interval='bygroup' you will get a new worksheet for every by group -- without regard to the BY group in any other procedure

I am curious about your statement about getting this to work for PDF with STARTPAGE=NO...when I do the following code in PDF, I do not get all the AGE=12 together and all the AGE=13 together. I get all the by groups for the first PROC PRINT followed by all the by groups for the second PROC PRINT.

How did you make your output come out differently??

cynthia
[pre]
ods listing close;
title;
** sort by age;
proc sort data=sashelp.class out=class;
by age;
run;

ods pdf file='c:\temp\PDF_BYGRP.pdf' startpage=no;

proc print data=class;
by age;
var name sex;
run;

proc print data=class;
by age;
var name height weight;
run;

ods _all_ close;
[/pre]
Contributor
Posts: 53

Re: ExcelXP - 2 procs on the same tab when both us by vars?

Sorry I should have been more specific. I have this working with a combo of a macro loop and the PDF destination using the startpage= option. Something like below but I can use the startpage= option in PFD to manually tell it when to start a new page.... In ExcelXP I can't seem to get that to work.... The code below puts Agent #1 and Agent #2 in the same tab..

data test;
input @001 policy_number $10.
@011 agent_name $25.
@036 balance 10.2
;
cards;
1111111111Agent Number One 1052.23
2222222222Agent Number One 2334.23
3333333333Agent Number One 2321.22
4444444444Agent Number Two 0033.21
5555555555Agent Number Two 0232.23
6666666666Agent Number Three 2132.00
7777777777Agent Number Four 3234.22
;
proc sort data=test;
by agent_name;
run;

%macro report(agent_name);

ods tagsets.ExcelXP options(sheet_name="&agent_name");

proc report data=test(where=(agent_name = "&agent_name")) MISSING nowindows SPLIT='*' HEADLINE contents = '';
by agent_name;
column policy_number balance;
define policy_number / 'Policy Number';
define balance / 'Balance';
run;

proc print data=test(where=(agent_name = "&agent_name"));
by agent_name;
run;

%mend report;



ods tagsets.ExcelXP path="&out_path" file="&out_file";
ods tagsets.ExcelXP options(sheet_interval='none');

%report(Agent Number One);
%report(Agent Number Two);

ods _all_ close; Message was edited by: FrankE
SAS Super FREQ
Posts: 8,743

Re: ExcelXP - 2 procs on the same tab when both us by vars?

Ah, that explains it.

Well, if you know macro coding, then your life will get easier. The "hardcoded" example that I had is shown below. I'm sure you can figure out how to get it working.

Note that the internal documentation for SHEET_INTERVAL (obtained by using doc='Help') says that:
[pre]
Sheet_Interval: Default Value 'Table'
Values: Table, Page, Bygroup, Proc, None.
This option controls how many tables will go in a worksheet.
In reality only one table is allowed per worksheet. To get more
than one table, the tables are actually combined into one.

Specifying a sheet interval will cause the current worksheet to close.
It is recommended that this always be the first option to insure that
The options following it apply to the new worksheet rather than the
last worksheet.

[/pre]

So, even if you respecify SHEET_INTERVAL='NONE' where you want a new sheet, it will close the current sheet and start a new sheet -- this is the equivalent of being able to put STARTPAGE=NOW with ODS PDF and ODS RTF.

With TAGSETS.EXCELXP, I believe the way you would get the outputs from 2 PROC PRINTS on the same worksheet would be to use WHERE processing to "simulate" by group processing (or using some kind of macro logic). The WHERE approach is shown in the program below. (Or else, as Peter suggested, rearrange your output objects with ODS DOCUMENT and PROC DOCUMENT.)

But, you still might have some finagling with ODS DOCUMENT, because what TAGSETS.EXCELXP is actually doing when you specify SHEET_INTERVAL='NONE' is combining your multiple separate tables into one "bigger" table.

Anyway, note that for this program, the two PROC PRINT steps both use SASHELP.SHOES. The first PROC PRINT displays different variables than the second PROC PRINT. The SHEET_INTERVAL='NONE' suboption puts both outputs on one worksheet. Then the ODS TAGSETS.EXCELXP statement with the new SHEET_NAME value and the SHEET_INTERVAL value does the equivalent of STARTPAGE=NOW for PDF by starting a new worksheet.

cynthia
[pre]
ods listing close;
title;
ods tagsets.ExcelXP file="c:\temp\onetab_per_region.xls" style=sasweb
options(doc='Help' sheet_interval='none' sheet_name="Africa");

proc print data=sashelp.shoes;
where region="Africa";
var region product sales;
run;

proc print data=sashelp.shoes;
where region="Africa";
var region subsidiary returns inventory;
run;

ods tagsets.Excelxp options(sheet_interval='none' sheet_name="Asia");

proc print data=sashelp.shoes;
where region="Asia";
var region product sales;
run;

proc print data=sashelp.shoes;
where region="Asia";
var region subsidiary returns inventory;
run;

ods _all_ close;
[/pre]
Contributor
Posts: 53

Re: ExcelXP - 2 procs on the same tab when both us by vars?

Awesome! Thanks for your help. For anyone else who may have this issue, below is the working example. To be practical, you'll have to create a dataset with 1 observation for each "Agent" and loop through them.. but here I hard coded "Agent Number One" and "Agent Number Two" for simplicity.

%let out_path = c:\temp\;
%let out_file = test.xls;

data test;
input @001 policy_number $10.
@011 agent_name $25.
@036 balance 10.2
;
cards;
1111111111Agent Number One 1052.23
2222222222Agent Number One 2334.23
3333333333Agent Number One 2321.22
4444444444Agent Number Two 0033.21
5555555555Agent Number Two 0232.23
6666666666Agent Number Three 2132.00
7777777777Agent Number Four 3234.22
;
proc sort data=test;
by agent_name;
run;

%macro report(agent_name);
ods tagsets.ExcelXP options(sheet_interval='none' sheet_name="&agent_name");

proc report data=test(where=(agent_name = "&agent_name")) MISSING nowindows SPLIT='*' HEADLINE contents = '';
by agent_name;
column policy_number balance;
define policy_number / 'Policy Number';
define balance / 'Balance';
run;

proc print data=test(where=(agent_name = "&agent_name"));
by agent_name;
run;
%mend report;


ods tagsets.ExcelXP path="&out_path" file="&out_file";

%report(Agent Number One);
%report(Agent Number Two);

ods _all_ close;
Ask a Question
Discussion stats
  • 5 replies
  • 703 views
  • 1 like
  • 3 in conversation