The SAS Output Delivery System and reporting techniques

tagsets.excelxp and Proc Report: Sheet Formatting/ Styling Questions

Reply
Frequent Contributor
Posts: 142

tagsets.excelxp and Proc Report: Sheet Formatting/ Styling Questions

Hi all,

I've written a program using tagsets.excelxp and proc report. There are a few formatting things/Styling  I’m having trouble figuring out how to program. I have attached a spreadsheet with examples of my output now and my desired output.

1.  In the media method column I need to vertical center the values “Direct Mail” and “Email” so they are centered to the month groups and remove the cell boarders. 

2.  The header for the totals is outputting “SubT” when it should be “Direct Mail Sub Total:”, “Email Sub Total:” and “Grand Total: “and the headers need to be bold and the “Grand Total:” total header needs to be an 11 point font.

3.  In both the subtotal rows I need the total  lift% calculated in the lift column, so ((test/control)-1).

4.  I would like a gray shaded row between the email subtotal and the grand total and make the top border of the grand total row thicker.

Below is what I have for my now program including sample data and again I’ve attached a spreadsheet with examples of my output now and my desired output.  Any help on any of these points will be greatly appreciated!


Thanks. 


Sample Data


data have;

length MEDIA_METHOD $13 Month_Year $15 Control 8  Test 8  Lift 8;

INPUT MEDIA_METHOD $1-13 Month_Year $14-27 Control Test Lift ;

format Lift percentn.;

  datalines;

Direct Mail  February 2014 1 1 0.00

Direct Mail March 2014    2 1 -0.50

Direct Mail April 2014    1 1 0.00

Direct Mail May 2014    3 3 0.00

Direct Mail June 2014    2 1 -0.50

Direct Mail July 2014    2 1 -0.50

Direct Mail August 2014 2 2 0.00

Direct Mail September 2014 2 2 0.00

Direct Mail October 2014 1 1 0.00

Direct Mail November 2014 2 2 0.00

Direct Mail December 2014 2 1 -0.50

Direct Mail January 2015 2 2 0.00

Email    February 2014 7 7 0.00

Email    March 2014    8 6 -0.25

Email    April 2014    6 6 0.00

Email    May 2014    8 6 -0.25

Email    June 2014    15 11 -0.27

Email    July 2014    11 6 -0.45

Email    August 2014 12 7 -0.42

Email    September 2014 11 8 -0.27

Email    October 2014 10 6 -0.40

Email    November 2014 11 7 -0.36

Email    December 2014 21 11 -0.48

Email    January 2015 13 7 -0.46

;


Program

%let rstart='February 2014';

%let rend='January 2015';

ods listing close;

options

rightmargin=.1in

leftmargin=.1in

Bottommargin=.1in

topmargin=.1in

Papersize='legal';

ods escapechar='^';

ods tagsets.excelxp file="C:\Users\Desktop\Monthly Test.xls"

style=minimal

options(sheet_interval='none' 

ORIENTATION= 'Landscape'

AUTOFIT_HEIGHT= 'yes'

embedded_titles= 'yes'

sheet_name="Average HH Touches");

title;

TITLE JUSTIFY=l FONT='Arial Narrow'  HEIGHT=12pt "^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Average Touches per Household ^S={}"  ;

Title2 JUSTIFY=l FONT='Arial Narrow'  HEIGHT=11pt "^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Reporting Period: &rstart to &rend^S={}" ;

proc report data=Hh_touches nowd

style(column)={just=c font_size=10pt font_face= 'Arial Narrow'}

style(header)={font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1};

column ("^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Average Touches per Household ^S={}" 

MEDIA_METHOD Month_Year  Control Test lift);

define MEDIA_METHOD / group

style(column)={just=left  font_size=10pt font_face= 'Arial Narrow'cellwidth=1.3in }

style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

define Month_Year / display

style(column)={just=left  font_size=10pt font_face= 'Arial Narrow'cellwidth=1.2in }

style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

define Control / display

style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }

style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

define Test  / display

style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }

style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

define lift  / display

style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

define MEDIA_METHOD / group;

define Control / sum;

define test / sum;

break after MEDIA_METHOD / summarize skip ol;

rbreak after / summarize skip ol;

compute after MEDIA_METHOD;

MEDIA_METHOD= catx(MEDIA_METHOD,' ', 'SubTotal');

endcomp;

run;

ods tagsets.excelxp close;

ods listing;

SAS Super FREQ
Posts: 8,719

Re: tagsets.excelxp and Proc Report: Sheet Formatting/ Styling Questions

Hi:

  A few notes:

-- HEADLINE, HEADSKIP, OL and SKIP are ignored by ODS destinations and only honored by the LISTING destination -- so there is no point in having them in your code.

-- the SPANROWS option on the PROC REPORT statement should give your MEDIA_METHOD column the look you want

-- Direct Mail SubTotal is a string that is 20 characters wide, but your length for MEDIA_METHOD is just $13 -- just as you cannot stuff 10 pounds of sugar in a 5 pound bag, you cannot stuff "Direct Mail SubTotal" into 13 characters.

--Given that Media_Method has a usage of GROUP and Month_Year has a usage of DISPLAY, you should probably be seeing a note like this in the log:

NOTE: Groups are not created because the usage of Month_Year is

DISPLAY. To avoid this note, change all GROUP variables to ORDER variables.

--In addition to the variable width issues, your syntax for the CATX function is wrong for creating the SubTotal string. Please see the screen shot for an example of the right syntax.

--You have 2 DEFINE statements for MEDIA_METHOD, both with GROUP usage, you only need 1 or the second one can clobber the first one; you have 2 DEFINE statements for CONTROL and TEST -- you only need 1 for each of them.

--To set a default font_face and font_size for the whole report, instead of putting those attributes on every DEFINE, you can put those attributes in the PROC REPORT statement and your code will be easier to read

--You can specify style(summary) to make the summary lines for SubTotal and Grand Total in bold. It looks to me like Grand Total is a different font size -- you can do that in the RBREAK statement.
     

  Here is the changed program that produced my results (after making Media_Method a width of $20 when it was created).

  

Cynthia


ods tagsets.excelxp file="C:\temp\MonthlyTest.xml"
  style=minimal
  options(sheet_interval='none'  ORIENTATION= 'Landscape' autofit_height='yes'
          embedded_titles= 'yes' sheet_name="Average HH Touches");
title;

TITLE JUSTIFY=l FONT='Arial Narrow'  HEIGHT=12pt "^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Average Touches per Household ^S={}";
Title2 JUSTIFY=l FONT='Arial Narrow'  HEIGHT=11pt "^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Reporting Period: &rstart to &rend^S={}";

proc report data=have nowd spanrows
   style(column)={just=c font_size=10pt font_face= 'Arial Narrow'}
   style(header)={font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1}
   style(summary)={font_size=10pt font_face='Arial Narrow' font_weight=bold};
    
   column ("^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Average Touches per Household ^S={}"
            Media_Method Month_Year  Control Test lift);
   define Media_Method / order
       style(column)={just=center font_weight=bold  cellwidth=1.3in vjust=m}
       style(header)={just=center FONT_WEIGHT =BOLD background=cxcccccc};
   define Month_Year / display
      style(column)={just=left   cellwidth=1.2in }
      style(header)={just=center    FONT_WEIGHT =BOLD background=cxcccccc};
   define Control / sum
      style(column)={just=center    cellwidth=.7in }
      style(header)={just=center    FONT_WEIGHT =BOLD background=cxcccccc};
   define Test  / sum
      style(column)={just=center   cellwidth=.7in }
      style(header)={just=center   FONT_WEIGHT =BOLD background=cxcccccc};
   define lift  / display
      style(column)={just=center  cellwidth=.7in}
      style(header)={just=center   FONT_WEIGHT =BOLD background=cxcccccc};
   break after Media_Method / summarize ;
   rbreak after / summarize style={font_size=12pt font_weight=bold font_face="Arial Narrow"};
   compute after MEDIA_METHOD;
        MEDIA_METHOD= catx(' ', MEDIA_METHOD,'SubTotal');
   endcomp;
   compute after;
        Media_Method = 'Grand Total';
   endcomp;
run;
ods tagsets.excelxp close;

ods listing;

Attachment
Attachment
Ask a Question
Discussion stats
  • 1 reply
  • 484 views
  • 1 like
  • 2 in conversation