topic Re: Need help to use bygroup option in Proc Report in ODS and Base Reporting

Need help to use bygroup option in Proc Report

Banu — Tue, 04 Apr 2017 11:39:50 GMT

Hello,

I needs to use ODS Tagsets ExcelXP bygroup option in Proc Report. How can I use group option in Proc Report. Please provide me your suggestions.

Re: Need help to use bygroup option in Proc Report

RW9 — Tue, 04 Apr 2017 12:30:56 GMT

Sorry, you have not really told us anything there. A good question is one that provides test data, in the form of a datastep (or from sashelp tables), and what the output should look like. Also, any code you have done towards it or logic needed.

From what I can decode from your post you are talking about two different things. By group processing does the whole procedure for each by group, define/group sums just block out and summarise data within the one procedure. Now for by group something like:

ods tagsets.excelxp file="s:\temp\rob\test.xml" options(sheet_interval="bygroup");
proc sort data=sashelp.class out=class;
  by sex;
run;
proc report data=class;
  by sex;
  title '#byval1';
run;
ods tagsets.excelxp close;

Re: Need help to use bygroup option in Proc Report

Cynthia_sas — Tue, 04 Apr 2017 13:31:14 GMT

Hi:
I believe there have been other forum postings on the use of BYGROUP processing with TAGSETS.EXCELXP -- the proc you use is typically irrelevant. What people usually want is for the #byval to be used for the sheet_name when they have each BY group start on a different sheet and there is a TAGSETS.EXCELXP option for that -- if you put options(doc='Help') in your ODS invocation statement, you'll see the suboption to use. Otherwise, you could search on the forum for previous examples.

cynthia

Re: Need help to use bygroup option in Proc Report

Banu — Tue, 04 Apr 2017 17:24:23 GMT

Thank you so much RW9 and Cynthia.

Below is my scenario.
Source:

EmpName Empno Sal DeptName
100 Santa 1000 Maths
101 Frank. 2000 Maths
102 Easter 3000 Science
103 Quincy. 4000 History
104 Yesk. 5000 Science

Output:

Excel sheet Name: Class Summary.xlsx
1st tab: Maths

EmpName Empno DeptName Sal
100 Santa Maths 1000
101 Frank Maths 2000
-----------
3000

In the same way,Need Excel sheet tabs name should be Department Names. And each tab displays about the each department details.Need all this information in excel sheet.

I got idea on bygroup in ODs tagsets excelxp , and it's easy to use with Proc print. As per my requirement which procedure is suitable to send group information into excel sheet tabs.Thanks for your info.

Re: Need help to use bygroup option in Proc Report

RW9 — Tue, 04 Apr 2017 17:40:25 GMT

Sorry, the question is not clear. Proc report is just a more advanced version of proc print. Wether you use one or the other is up to you. Me, I use proc report for all output. By grouping works on both those, and in fact almost all procedures.

Re: Need help to use bygroup option in Proc Report

Cynthia_sas — Wed, 05 Apr 2017 03:33:36 GMT

Hi:

Well, PROC REPORT or PROC PRINT doesn't matter, what matters is the options and suboptions you use. For example, this code works fine for me to name each sheet with the value of the BY group for that sheet.

proc sort data=sashelp.class out=class;
  by age;
  where age in (12, 13, 14);
run;

options nobyline;

ods excel file='c:\temp\bygrp_excel_sheet_title.xlsx' style=htmlblue
    options(sheet_interval='bygroup' sheet_name="Age: #byval1" embedded_titles='yes');
	  
ods tagsets.excelxp file="c:\temp\bygroup_one_sheet_title.xml" style=htmlblue 
    options(sheet_interval='bygroup' sheet_name="Age: #byval1" embedded_titles='yes');
    
    proc report data=class; 
      by age;
	  column name sex age height weight;
	  title 'Report for Age: #byval(age)';
    run;
ods tagsets.excelxp close;
ods excel close;

Since you did not really provide data nor information about the destination you were using, I just used SASHELP.CLASS and ODS EXCEL and ODS TAGSETS.EXCELXP as proof of concept. You will at least need 9.4 of SAS to run the ODS EXCEL example, but the TAGSETS.EXCELXP example should work in 9.3 as well.

cynthia

Re: Need help to use bygroup option in Proc Report

Banu — Wed, 05 Apr 2017 06:59:28 GMT

Thank you so much for all your response. I got the info for what I require.

Re: Need help to use bygroup option in Proc Report

Banu — Thu, 06 Apr 2017 18:30:21 GMT

Could you please let me know what is the meaning of
below default column width option in ods tagsets.excelxp*(Would like to know what's the meaning of all these values)

default_column_width='17.14, 21.57, 17.71, 18.29, 17.29, 15.29, 18.02, 15.86, 16.57, 47.57' width_fudge='0.55');

Re: Need help to use bygroup option in Proc Report

Cynthia_sas — Thu, 06 Apr 2017 23:53:25 GMT

Hi:
Please add
options(doc='Help' ... other options) to your ODS TAGSETS.EXCELXP and you can read about the default_column_widths suboption.

cynthia