topic Re: add dummy across columns (header)in proc report in SAS Programming

add dummy across columns (header)in proc report

snip — Mon, 02 Oct 2023 18:52:20 GMT

data AA;
  infile datalines dlm=',';
  input Stn $ avg1 avg2 avg3 avg4;
datalines;
NE,11111,11111,11110,5555
NW,22222,11119,22223,2322
SE,33333,33333,33331,2309
SW,44444,44444,44444,1100
;
run;
   
proc report data=AA;

column stn avg1 avg2 avg3 avg4;
define stn / order;
define avg1 /analysis;
define avg2 / analysis;
define avg3 / analysis;
run;

I want to add dummy columns for an aesthetic display, they must merge above the other columns and I must be able to manage the font size, background color, etc. of these columns, see the example. is it possible to do this please? Thank you for your help

Re: add dummy across columns (header)in proc report

ballardw — Mon, 02 Oct 2023 19:46:31 GMT

Please stop putting discussion into text boxes. That is what the main message window is for. Text boxes are for code or data text.

For this:

   
proc report data=AA;

column  stn ("heading one" avg1 avg2) ("heading two" avg3 avg4);
define stn / order;
define avg1 /analysis;
define avg2 / analysis;
define avg3 / analysis;
run;

Note that the ( ) are around both the column heading text and all of the columns to span.

Re: add dummy across columns (header)in proc report

snip — Mon, 02 Oct 2023 20:19:33 GMT

I am really sorry for the inconvenience caused, I have just recently started using this site,and I admit that I do not yet master the blocks. Can you explain the concept to me?

-Concerns my question on sas, how and where I can indicate the style of these two columns (font_size, background...)

Re: add dummy across columns (header)in proc report

Cynthia_sas — Mon, 02 Oct 2023 20:32:56 GMT

Hi:

Take a look at this paper https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf especially at the examples on for Output 12, 13 and 14. The discussion starts at the bottom of page 11.

Cynthia

Re: add dummy across columns (header)in proc report

ballardw — Mon, 02 Oct 2023 20:59:57 GMT

Which concept? The ( ) to create a group of items?

The whole bit of controlling appearance one element at a time is complex and time consuming because there are so many moving parts.

Example of minor basic changes

ods escapechar='^'; 
proc report data=AA;
column  stn ("^S={foreground=red background=green}Heading one" avg1 avg2) 
            ("^S={fontfamily='Times New Roman' fontsize=16pt fontweight=bold}heading two" avg3 avg4);
define stn / order;
define avg1 /analysis;
define avg2 / analysis;
define avg3 / analysis;
run;

The ODS Escapechar option sets a specific character, your choice but should be something not commonly used elsewhere, as a flag to tell SAS that you are going to request style overrides.

The ^S= is the start of a style override the { to } are the specific elements of the override. The example sets colors in one and the second sets font options.

Search your on-line help for "Using ODS Styles with Base SAS Report Writing Procedures" for examples.

Styles can be applied to cells, cell boarders, columns, variables, headers, column / row headers separately, total lines.

Procs Print, Report and Tabulate have similar but sometimes quite different details because the procedures behave differently.

Re: add dummy across columns (header)in proc report

snip — Tue, 03 Oct 2023 10:39:57 GMT

Thank you

1) why is there a void around my value? I want the background to be on the entire “heading on” cell

2) in the attached images the sas output contains the formatting (background and fore ground), on the other hand the output with ods excel, my excel file did not take the background into account and I need this output

is there a reason ?

how to correct this please

Re: add dummy across columns (header)in proc report

Ksharp — Tue, 03 Oct 2023 11:15:25 GMT

/*
Using Traffic Light Skill
*/
data AA;
  infile datalines dlm=',';
  input Stn $ avg1 avg2 avg3 avg4;
datalines;
NE,11111,11111,11110,5555
NW,22222,11119,22223,2322
SE,33333,33333,33331,2309
SW,44444,44444,44444,1100
;
run;
   
proc format;
value $back
'heading one'='green'
'heading two'='blue'
;
value $fore
'avg1','avg2'='yellow'
'avg3','avg4'='red'
other='white'
;
run;

ods excel file='c:\temp\x.xlsx' ;
proc report data=AA nowd style(header)=header{background=$back. foreground=$fore.};
column  stn ("heading one" avg1 avg2) ("heading two" avg3 avg4);
define stn / order style(header)={background=cxEDF2F9};
define avg1 /analysis;
define avg2 / analysis;
define avg3 / analysis;
run;
ods excel close;

Re: add dummy across columns (header)in proc report

snip — Tue, 03 Oct 2023 13:17:34 GMT

Thank you for taking the time to read my issues, and thank you for responding, you helped me a lot, I am very grateful. I have a question:

Can we merge a total across several group by columns? still with ods excel output

Re: add dummy across columns (header)in proc report

ballardw — Tue, 03 Oct 2023 14:41:05 GMT

@snip wrote:

Thank you for taking the time to read my issues, and thank you for responding, you helped me a lot, I am very grateful. I have a question:

Can we merge a total across several group by columns? still with ods excel output

You will need to clarify what you mean by "total across several by columns". As in show a before and after appearance.

If you meant to SUM numeric values, likely yes as Proc Report does allow some custom calculations but depending on data and the structure of the entire report may or may not be easy. Some custom report features require changes to the data to allow such.

Re: add dummy across columns (header)in proc report

snip — Tue, 03 Oct 2023 17:04:29 GMT

Hi,

I want a total this way If it's possible in the middle

Thank's

data AA;
  infile datalines dlm=',';
  input Stn1 $ stn2 $ stn3 $ stn4 $ avg1 avg2 avg3 avg4;
datalines;
NE,CC,A1,Z2,11111,11111,11110,5555
NE,CC,A1,Z2,11111,11111,11110,5555
NE,SS,A2,N5,11111,11111,11110,5555
TT,HH,QQ,T4,22222,11119,22223,2322
TT,HH,OO,M1,33333,33333,33331,2309
SE,ZZ,KK,P6,44444,44444,44444,1100
;
run;

ods excel file='c:\temp\x.xlsx' ;
proc report data=AA nowd style(header)=header{background=$back. foreground=$fore.};
column  Stn1 stn2  stn3  stn4 avg1 avg2 avg3 avg4 ;
define stn1 / order ;
define stn2 / order ;
define stn3 / order ;
define stn4 / order;
define avg1 /analysis;
define avg2 / analysis;
define avg3 / analysis;
define avg4 / analysis;

break after stn1 / summarize ;
compute after stn1; 
stn1='GLOBAL';

line ' '  ;
call define(_row_,'style','style=[ background=white ]');
endcomp;
/* FIN CALCUL DES TOTAUX */

run;

ods excel close;
run;
ods excel close;

Re: add dummy across columns (header)in proc report

ballardw — Tue, 03 Oct 2023 17:36:23 GMT

This sort of works in HTML output. I'm not sure that Excel will display it the same.

This places the text "Global" in STN2 so it is not on the left border but makes that column wider. The style overrides remove the borders by appearance but may still appear in Excel. Or leave Global in the leftmost column using the Stn1='Global'

proc report data=AA nowd style(header)=header{background=$back. foreground=$fore.};
column  Stn1 stn2  stn3  stn4 avg1 avg2 avg3 avg4 ;
define stn1 / order ;
define stn2 / order ;
define stn3 / order ;
define stn4 / order;
define avg1 /analysis;
define avg2 / analysis;
define avg3 / analysis;
define avg4 / analysis;

break after stn1 / summarize ;
compute after stn1; 
   stn1='';
   stn2='GLOBAL';
   call define('stn1','style','Style=[ borderrightwidth=0]');
   call define('stn2','style','Style=[borderleftwidth=0 borderrightwidth=0]');
   call define('stn3','style','Style=[borderleftwidth=0 borderrightwidth=0]');
   line ' '  ;
   call define(_row_,'style','style=[ background=white ]');
endcomp;
/* FIN CALCUL DES TOTAUX */

run;

For what it may be worth, the more custom you want a report the more work is needed and it may get to where Proc Report won't do. The data step has a feature called the Report Writing Interface that allows you do stuff like spanning based on other rules and actually spans unlike this example. However, since it is dealing at a very low level to allow that sort of control the syntax is much more complicated.

Re: add dummy across columns (header)in proc report

Ksharp — Wed, 04 Oct 2023 02:23:36 GMT

OK . You need some advanced skill a.k.a RWI and screw your dataset.

data AA;
  infile datalines dlm=',' truncover;
  input Stn1 :$20. stn2 $ stn3 $ stn4 $ avg1 avg2 avg3 avg4;
datalines;
NE,CC,A1,Z2,11111,11111,11110,5555
NE,CC,A1,Z2,11111,11111,11110,5555
NE,SS,A2,N5,11111,11111,11110,5555
GLOBAL,33,44,55,66
TT,HH,QQ,T4,22222,11119,22223,2322
TT,HH,OO,M1,33333,33333,33331,2309
GLOBAL,33,44,55,66
SE,ZZ,KK,P6,44444,44444,44444,1100
GLOBAL,33,44,55,66
;
run;

ods excel file="c:\temp\spantest.xlsx"   ;

title 'How to span Header Rows amd Data Rpws with RWI and DATA step';
data _null_; 
  set AA end=last; 
  if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
      obj.head_start(); 
	  ** Header row 1;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "heading one", column_span:4,style_attr:"color=black backgroundcolor=gold fontweight=bold"); 
      obj.format_cell(text: "heading two",column_span:4, style_attr:"color=white backgroundcolor=blue fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
	  ** Header row 2;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Stn1",  style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "Stn2", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "Stn3", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "Stn4", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "avg1", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "avg2",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "avg3", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "avg4",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
      obj.row_start(); 
if Stn1='GLOBAL' then do;
      obj.format_cell(data: Stn1, column_span:4,style_attr:"color=black fontweight=bold"); 
end;
else do;
      obj.format_cell(data: Stn1, column_span:1); 
end;
      obj.format_cell(data: Stn2, column_span:1); 
      obj.format_cell(data: Stn3, column_span:1); 
      obj.format_cell(data: Stn4, column_span:1); 
      obj.format_cell(data: avg1, column_span:1); 
      obj.format_cell(data: avg2, column_span:1); 
      obj.format_cell(data: avg3, column_span:1); 
      obj.format_cell(data: avg4, column_span:1); 
      obj.row_end(); 
  if last then do; 
      obj.table_end(); 
    end; 
run;
footnote;title;
ods excel close;

Re: add dummy across columns (header)in proc report

snip — Thu, 05 Oct 2023 18:05:45 GMT

you are a genius,

you managed to pull off something very difficult,

I can never thank you enough.

a big thank-you