topic How can I remove column name in group by sum row instead of group by value I want to show Total. in ODS and Base Reporting

How can I remove column name in group by sum row instead of group by value I want to show Total.

anil2 — Wed, 19 Sep 2018 12:40:32 GMT

instead of SUB value 'Agree services and credit' i want to show TOTAL word in group by sum row the issue is each SUB value is repeat again in group by sum row ,please help how can i remove that value.



PROC REPORT DATA=CCG.RICAGO_WEEKLY_MIS_FINAL out=summary_table_result LIST  
/*style=[cellwidth=.1 in rules=group frame=void] */
style(header)=[background= #BBDEFB foreground=black]
style(summary)=[background=white   foreground=black font_weight=bold]
style={font_size=10pt font=('Calibri') width=600 cellpadding=2 borderwidth=0 bordercolor=white}
 ;

COLUMN SBU entity (Module) 'SBU Wise Total (All modules) 'n 'Ageing Report'n ;
 DEFINE SBU / GROUP center style=[foreground=black font_weight=bold  borderleftwidth=2 borderrightwidth=2] ;
 define entity / group center style=[foreground=black font_weight=bold  borderleftwidth=2 borderrightwidth=2] ;
 DEFINE Module /  ACROSS  center style =[borderleftwidth=1 borderrightwidth=1];
/*DEFINE count /  ACROSS  style =[borderleftwidth=1 borderrightwidth=1];*/

 DEFINE 'Ageing Report'n / ACROSS center    style =[borderleftwidth=1 borderrightwidth=1 ];
 define 'SBU Wise Total (All modules) 'n /computed center style=[font_weight=bold foreground=black ] ;
break after SBU /summarize dol dul skip;

COMPUTE SBU ;
IF _break_='_RBREAK_' THEN
 CALL DEFINE('SBU','style','style=[pretext="Total "]');
ENDCOMP;

  compute 'SBU Wise Total (All modules)'n;
    'SBU Wise Total (All modules)'n=sum(input(_c2_,best32.),
			  input(_c3_,best32.),
			  input(_c4_,best32.),
			  input(_c5_,best32.),
			  input(_c6_,best32.),
			  input(_c7_,best32.),
			  input(_c8_,best32.)
			  
			  );

			  IF 'SBU Wise Total (All modules) 'n ^= ' '
THEN CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=#FBFCFC]");
/*			  call define(_row_,'style','style={borderrightwidth=4 borderrightcolor=black}'); */
 endcomp;


endcomp;


	  compute entity;
      count+1;
      if mod(count,2) then do;
         call define(_row_, "style", "style=[background=#E8EAF6]");
      end;
	  else do;
	  	call define(_row_, "style", "style=[background=#BDC3C7]");
		END;
   endcomp;

   rbreak after / summarize;



RUN;

Re: How can I remove column name in group by sum row instead of group by value I want to show Total.

Cynthia_sas — Wed, 19 Sep 2018 13:39:57 GMT

Hi:
You don't show the grand total line at the RBREAK, does your PRETEXT work there?

Is SBU a character or numeric variable. I tend only to use the PRETEXT method with numeric variables. I generally use the simple method with character variables.

Here's an example of the simple method, without using PRETEXT or CALL DEFINE (highlighting was not part of SAS code. I added it to show which statements impacted which report rows):

Cynthia

Re: How can I remove column name in group by sum row instead of group by value I want to show Total.

anil2 — Wed, 19 Sep 2018 14:12:01 GMT

Thanks for @Cynthia_sas,

Your demo Example is related to my issue but i dont want to show 'Total For Asia ' entire word want to show only Total instead of 'Total For Asia ' .

SBU column is character datatype . i have show the grand total Below but i have not paste entire image for that reason you are not able to show below grand total .

Re: How can I remove column name in group by sum row instead of group by value I want to show Total.

Cynthia_sas — Wed, 19 Sep 2018 14:24:45 GMT

Hi:

That is an equally simple thing to do with an assignment statement instead of using the CATX function:

_BREAK_ holds the NAME of the variable at the break. In my case, I am testing to see if the break is for REGION. In your case, your test would be for the variable named in your BREAK statement.

Cynthia

Re: How can I remove column name in group by sum row instead of group by value I want to show Total.

anil2 — Wed, 19 Sep 2018 17:33:17 GMT

Thanks Again @Cynthia_sas ,

This code is working fine i am getting expected output.

Thanks Again,

Anil Patil