The SAS Output Delivery System and reporting techniques

EXCELXP Style for row

Reply
Super Contributor
Posts: 359

EXCELXP Style for row

I had a very simple spread sheet consisting of a style definition and a PROC PRINT;

Our customer has now asked for several custom rows above the header of the PROC PRINT. I am looking at either another proc print or a data _null_ with sheet_interval='none'

I have 2 problems, first I would like to not have the header row for the first output to appear at all. I can set it to null labels, but the row remains.
Second, I would like to specify a different style for these rows. (new background color etc) I am hitting a brick wall on this one. Feel brain dead today.

Any ideas.
SAS Super FREQ
Posts: 8,742

Re: EXCELXP Style for row

Hi:
Have you looked at PROC REPORT??? I'm not sure what you mean by " not have the header row for the first output to appear at all" since I'm not sure what you mean by "first output". But, the code below does put some spanning headers into the report table and makes those headers have different style attributes using STYLE= overrides.

cynthia
[pre]
ods tagsets.excelxp file='spanhdr.xml' style=sasweb;

proc report data=sashelp.class nowd
style(header)={foreground=black background=pink};

column ('Spanning Header 1'
("Span 2" Name Sex)
("Span 3" Age Height Weight));
define name / style(header)={foreground=purple background=yellow};
define sex / style(header)={foreground=purple background=yellow};
define age / style(header)={foreground=cyan background=beige};
define height / style(header)={foreground=cyan background=beige};
define weight / style(header)={foreground=cyan background=beige};
run;
ods tagsets.excelxp close;
[/pre]
Super Contributor
Posts: 359

Re: EXCELXP Style for row

What I mean by " not have header at all" is that I need three rows, with 2 columns each without a header (name/label) row above them. I have not seen an option in Proc Report to leave out this row.

The data block that comes from the existing proc print places the data header correctly, but I just want to add three rows above it. thus the style for the output of TEMP would be different from that of PS.

It seems I am missing something simple here as all I want to do is control the style of these cells.

ods listing close;

ods tagsets.excelxp file= excelout style=Styles.packing
options ( ABSOLUTE_COLUMN_WIDTH = "25,25,8" gridlines = 'Yes' sheet_interval='none' );
title;

ods tagsets.excelxp style=Styles.packing;

/* I would like to add different styles to this block also would be nice to get rid of the extra header row the header row for the next block must come after these rows*/
data _null_;
set temp;
file print
ods=(variables=(data(label=' ' ) filename(label=' ')));
put _ods_;
run;

/* Below is the existing program that gives me the data portion of the report correctly*/
proc print data=ps noobs;

run;

ods tagsets.excelxp close; This is an idiotically short and useless piece of output that MUST be formatted exactly as they want ;{


Message was edited by: Flip
SAS Super FREQ
Posts: 8,742

Re: EXCELXP Style for row

Hi:
If you're placing the headers with DATA _NULL_, you may need a custom table template in order to get the Headers for DATA _NULL_ to be different than the default headers for the rest of the output.

But, before you go down that road, can you do an example using SASHELP.CLASS instead of showing WORK.TEMP and WORK.PS????

cynthia
Super Contributor
Posts: 359

Re: EXCELXP Style for row

Easier than trying to use different data this is the actual data with text replacement. Yes this is the entire data.
I just want to control the style of those first 3 rows and have nothing above them. For that matter the part of the report for data set PS could be ignored as it runs fine.

data ps;
rundt = datetime();
length data $45;
DATA = 'TEXT FOR THE FIRST COLUMN';
FILENAME = "File_being_delivered.txt";
COUNTS = 567890 ;
run;

data temp;
length data $175;
length filename $45;
label data = ' ';
label filename = ' ';
DATA = "For Fixed Length Files: Record Length";
FILENAME = '';
output;
DATA = 'For Delimted Files: The # of Delimiters:';
FILENAME = '20';
output;
DATA = "For Delimted Files: The Character Used as a Delimiter:";
FILENAME = '|';
output;
run;
SAS Super FREQ
Posts: 8,742

Re: EXCELXP Style for row

Hi:
How about the NOHEADER option in PROC REPORT??? Using your same data.

cynthia
[pre]
ods listing close;
filename excelout 'c:\temp\stuff2.xml';
ods tagsets.excelxp file= excelout style=Styles.sasweb
options ( ABSOLUTE_COLUMN_WIDTH = "25,25,25"
gridlines = 'Yes' sheet_interval='none' );
title;

proc report data=temp noheader nowd;
column data filename;
run;

proc print data=ps noobs;
run;

ods tagsets.excelxp close;
[/pre]
Super Contributor
Posts: 359

Re: EXCELXP Style for row

I knew there had to be a simple way. Senility is seeping in.
Ask a Question
Discussion stats
  • 6 replies
  • 198 views
  • 0 likes
  • 2 in conversation