How do i create a break with in Zone(RBreak) like Region, Cluster,Branch

[Ramgod]

compute Mega_Zone;
if _BREAK_="_RBREAK_" then Mega_Zone="Grand Total";
if _BREAK_="Mega_Zone" then Mega_Zone=catx(" ",Mega_Zone,"Total");
endcomp;

compute Zone;
if _BREAK_="Zone" then do;
Zone=catx(" ",Zone,"Total");
Mega_Zone="";
end;
endcomp;

compute Region;
if _BREAK_="Region" then do;
Region=catx(" ",Region,"Total");
Zone="";
Mega_Zone="";
end;
endcomp;

compute Cluster;
if _BREAK_="Cluster" then do;
Cluster=catx(" ",Cluster,"Total");
Region="";
Zone="";
Mega_Zone="";
end;
endcomp;

Break after Cluster/Summarize style=[background=maroon foreground=white];
Break after Region/Summarize style=[background=maroon foreground=white];
Break after Zone/Summarize style=[background=maroon foreground=white];
Break after Mega_Zone/Summarize style=[background=maroon foreground=white];
RBreak after /Summarize style=[background=maroon foreground=white];
run;

[Cynthia_sas]

Hi:

I'm not sure what your question is. I don't actually see Mega_zone on your XLS example/data. I only see Zone, Region (Regon), Cluster and Name. This is the type of report, where it's necessary to see ALL your code (including ODS statements) and have a sample of your data (or use a SASHELP dataset that is available to everyone) in order to figure out how to help you. It seems that you want to concatenate the word "Total" to Zone, Region and Cluster values.

  The COLUMN statement is important to understanding what you have and what you want. Does Mega_zone appear first on the COLUMN statement or if, in another position, where? Generally speaking the DEFINE statement is also important -- what is the usage for your items -- ORDER or GROUP? Do you have a BY statement?

  I'm also confused by your XLS example -- is it your desired output or is it the output you are generating with working code? What you posted doesn't make sense. It looks like all group or order variables with no analytic items on the report.

  If you cannot post data or all your code, then you might want to open a track with Tech Support, They can look at all your code and all your data and  help you figure out, based on your desired destination, how to help you achieve the type of breaking you want.

cynthia

[Ramgod]

Hi,

Xls file givenas attachment was the output i am expecting. Mega zone is the first column defined which was missed in the file attached, I am unable to share complete stream with you in open forum

Please find below the proc report  with colum and define statement for your understanding

proc report data=MIS out=Ram spanrows
STYLE(REPORT)=[cellspacing=2 borderwidth=3 TAGATTR="NOWRAP" FONT=(ZurichBT, 10PT) font_weight = bold]
STYLE(Header)=[FOREGROUND=WHITE BACKGROUND=maroon FONT=(ZurichBT,10PT) FONT_WEIGHT=BOLD TAGATTR="WRAP"]
STYLE(COLUMN) = [TAGATTR="NOWRAP" outputwidth=0.30in background=lightyellow FONT=(ZurichBT,10PT)];
COLUMN Mega_Zone Zone Region Cluster Name #applns

define Mega_Zone /"Mega Zone" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=center] ;
define Zone/"Zone" group     style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];
define Region/"Region" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];
define Cluster/"Cluster" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];
define Name/"Name" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];
define #applns/"No of applications" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];

[Cynthia_sas]

Hi:

  Is this working code? The variable #applns is not a valid variable name. And, the usage for #applns doesn't make sense either -- why would a numeric variable be treated as a GROUP item? There's nothing in your column statement to be summarized at the break.

  In addition, your column statement doesn't end with a semi-colon. The code doesn't have any BREAK or RBREAK statements. The code doesn't have a step boundary. You don't show HOW you are creating output for Excel (ODS HTML, ODS MSOFFICE2K or ODS TAGSETS.EXCELXP). And, if you can't post data in 'open stream', then that is another reason to work with Tech Support, because they can maintain the confidentiality of your data.

  On the other hand, it would be quite possible to mock up some fake data using sashelp.prdsale or sashelp.cars to illustrate your question.

cynthia

[Ramgod]

Hi

Pls find attached sample output format required.  With the below code i am not getting the required break in the report.

Also guide me with some reading material on breaks and rbreaks to customise the output as required.

proc report data=sashelp.cars out=temp spanrows

STYLE(REPORT)=[cellspacing=2 borderwidth=3 TAGATTR="NOWRAP" FONT=(ZurichBT, 10PT) font_weight = bold]

STYLE(Header)=[FOREGROUND=WHITE BACKGROUND=maroon FONT=(ZurichBT,10PT) FONT_WEIGHT=BOLD TAGATTR="WRAP"]

STYLE(COLUMN) = [TAGATTR="NOWRAP" outputwidth=0.30in background=lightyellow FONT=(ZurichBT,10PT)];

COLUMN Origin Type Make Model Drivetrain MSRP Invoice;

define Origin/"Origin" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];

define Type/"Type" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];

define Make/"Make" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=center] ;

define Model/"Model" group     style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];

define Drivetrain/"Drivetrain" group style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];

define MSRP/"MSRP" sum style(column)=[outputwidth=1.2in cellwidth=1.2in just=left];

define Invoice/"Invoice" Sum format=comma10.2 style(column)=[outputwidth=1.2in cellwidth=1.2in just=Center];

compute Origin;

if _BREAK_="_RBREAK_" then Origin="Grand Total";

endcomp;

Break after Make/Summarize style=[background=maroon foreground=white];

Break after Type/Summarize style=[background=maroon foreground=white];

RBreak after /Summarize style=[background=maroon foreground=white];

run;

[Cynthia_sas]

Hi:

  I think I see what you're experiencing. You are limited to the length of the ORIGINAL variable. I think Origin is a length of $6, so you would only see "Grand", and not "Grand Total". I don't exactly see how your posted code produced the output in the XLS file, but I already had an example that used SASHELP.CLASS, so I'm posting it below. You can see that the SEX variable only shows 'G' at the break in report #1, while with the NAME variable, the break shows "Grand To" because NAME is a length of $8. Then #3a and #3b show two possible workarounds.

  As for recommended reading, I like this topic in the doc entitled, "How PROC REPORT Builds a Report" and this paper (http://support.sas.com/resources/papers/ProcReportBasics.pdf) and the PROC REPORT "Concepts" section is good too.

cynthia

ods html file='c:\temp\compare_total.html' style=sasweb;
  
proc report data=sashelp.class;
  column sex age height weight;
  title '1) Will Only See "G" in the break line';
  title2 'because length of SEX var is $1';
  define sex / order;
  define age / order;
  define name / order;
  compute sex;
    if _break_ = "_RBREAK_" then do;
       sex = 'Grand Total';
    end;
  endcomp;
  rbreak after / summarize;
run;
  
proc report data=sashelp.class;
  column name age height weight;
  title '2) Will Only See "Grand To" in the break line';
  title2 'because length of NAME var is $8';
  define name / order;
  define age / order;
  compute name;
    if _break_ = "_RBREAK_" then do;
       name = 'Grand Total';
    end;
  endcomp;
  rbreak after / summarize;
run;
   
proc report data=sashelp.class;
  column name age height weight;
  title '3a) Will Now See "Grand Total" in the break line';
  title2 'Because the PRETEXT element is added without regard to the variable length';
  title3 'This technique might not work in all situations';
  define name / order;
  define age / order;
  compute name;
    if _break_ = "_RBREAK_" then do;
       call define(_col_,'style','style={pretext="Grand Total"}');
    end;
  endcomp;
  rbreak after / summarize;
run;
   
proc report data=sashelp.class;
  column name showname age height weight;
  title '3b) Will Now See "Grand Total" in the break line';
  title2 'This uses a COMPUTED item with a length big enough for the string';
  define name / order noprint;
  define showname / computed;
  define age / order;
  compute showname/character length=12;
    if _break_ = "_RBREAK_" then do;
       showname = "Grand Total";
    end;
    else showname = name;
  endcomp;
  rbreak after / summarize;
run;
ods html close;