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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.