242017
 

Editor's note: Amanda Farnsworth is Head of Visual Journalism at BBC News and a featured speaker at SAS Global Forum 2017, April 2-5, 2017 in Orlando.

There was a best selling book some years ago called “Men are from Mars and Women are from Venus.” It’s a phrase I thought about a lot when I first started my current job, not so much in the gender sense, but because it can be really challenging to bring together teams with very different experiences, skillsets and, above all, cultures.

Different strokes

In 2013, I was asked to form a new department – Visual Journalism – bringing together Online Designers, TV Designers and Online Journalists with an aptitude for graphics and visuals as well as Developers who worked with me but not for me. These included people staffing the many Language Services of the BBC World Service. The different teams produced content for the BBC News website, TV 24hr News Channels and Bulletins.

And boy, were they all different!

The digital folk were very creative but in a controlled way.  They worked with a set visual design language and liked to do this as a structured process which involved a lot of user testing focusing on what the audience would understand and how would they behave when faced with some of our visual content.

Meanwhile, those with a TV background liked to work in a much more fluid way – creative workshops and experimentation - with less audience focus, as it was so much harder to get proper audience feedback on the visual elements of a TV report that viewers would get a single chance to see and take in.

And, as everyone who has gone through change knows, it can be a scary and difficult time for many of those involved. I have always found The Transition Curve one of the most useful things I ever learnt in a management course, helping me to identify how different parts of the team might be handling change. I stuck this diagram on a filing cabinet next to my desk when I created the Visual Journalism team.

And there was one other thing: I had a predominantly TV background and I was now being asked to lead a team that was packed full of digital experts. I’d always prided myself on my technical as well as editorial ability, and now I was less technically skilled than most of the people working for me. How was I going to cope with that?

New beginnings

The first thing I did was to offer 30-minute one-on-ones with my staff.  About 60% agreed. I sent them a questionnaire to fill in in advance and asked them these  three questions:

1.    What Single Thing could we do very quickly that would change things for the better?

2.    How can the new Visual Journalism team work better together?

3.    What new tools do you need to do your job?

It proved to be a treasure trove of information with some interesting thoughts and great suggestions – here are a few examples of Q1 answers:

“A single management structure for the whole team. Sometimes different disciplines within the team clash as they are pulled in different directions by the priorities of their respective managers. This wastes time and creates unnecessary tensions.”

“Unfortunately we have a rather corrosive habit of 'rumour control' which is usually of a negative nature, particularly in this time of change and uncertainty. I think 'rumour control' can easily be reduced by providing as much information as possible ( good and bad ) so none is left to be made up!”

“I would like to see a re-evaluation of the planning area. A map that just happens to be going on air tomorrow, Should that be taking up a slot in planning? Maybe planning should be more focussed on projects that are moving us and our journalism forward. “

And from question 2:

“One word: flexibility. The teams need to absorb the concept that we have one goal, the individual outputs need to grasp this to. A respect for the established disciplines is all well and good, but tribalism needs to be left behind.”

So I had a lot of face time with a lot of staff.  They all appreciated the dedicated time, but it also gave me a chance to meet them individually. The questionnaires gave me a written record of all their top concerns which I could refer to in the coming months and use as a justification or guide for change. And I could say after six months that I had done a lot of the things they had asked for along with other things that I felt needed to be done.

In addition, I wanted the teams to meet each other.  So, we held a Speed Dating session. We made two long rows of chairs facing each other and sat TV people on one side and online people on the other.  They had one minute to say what they did and one minute to listen to the person opposite them share the same before I sounded a horn and everyone moved down one chair. It was a bit chaotic and a little hysterical to watch, but proved to be a great way of breaking the ice between the teams.

After a month in which I also immersed myself in the work of the various teams with a series of show and tells and shadowing days, and asked external stakeholders what they wanted from the new department, I drew up my vision.

It’s main message was that we were now a cross-platform team who needed to share ideas, information, skills and assets to create great, innovative content across TV and Digital.

The build phase

Even as we began the process of real change, the outside world suddenly started to move quickly. More and more of our news website traffic started to come from mobile, not desktop devices, and the distinction between what was TV and what was Digital began to blur, with the use of more video and motion graphics online. Social media platforms proliferated and became a key way of reaching an audience that didn’t usually access BBCTV or Digital content. We found ourselves on the cutting edge of where TV meets the web. And we had to make the most of it.

I began a series of internal attachments where online and TV designers learnt each other’s skills. I supplemented that with training so they could learn new software tools and design techniques. The lines between journalists and designers also began to fade, with many editorial people learning motion graphics skills for use on the increasingly important social media platforms.

I also encouraged and stood the cost of people spending a month outside the department learning how other parts of the BBC News machine worked and help spread the word that the new dynamic Visual Journalism department wanted to partner up and do big high impact, cross platform projects.

I revamped our Twitter feed, offered myself and other colleagues for public speaking at conferences and made sure we entered our best work for awards.

Quite quickly this all began to pay dividends. We won a big data journalism prize and we formed some big external partnerships with universities doing interesting research and with official bodies like the Office for National Statistics. We received a big investment for more data journalism from our Global division and from BBC Sport who wanted to do some big data led projects around the World Cup and Olympics.

Social glue was also important. We instituted a now legendary annual pot-luck Christmas lunch where the tables groaned with the amazing food people brought in to share. The Christmas jumpers are always impressive and we hold a raffle and quiz too.

There was, and still is, a major job to do just listening and looking after the staff. I make a point of praising and rewarding great work. We don’t have a great deal of flexibility on pay at the BBC, but rewards like attending international conferences, getting training opportunities and receiving some retail vouchers from the scheme the BBC runs all help.  I also always facilitate flexible working as much as is humanly possible, not just for women returning to work after maternity leave, but for caregivers, people who want to work part-time and most recently for two new dads who are going to take advantage of the paternal leave scheme and be the sole parent at home for six months while their wives return to work.

I also write an end-of-year review and look ahead to the next 12 months that I send to all staff.  It outlines achievements and great content we have made but also the aims, objectives and challenges for the year ahead.

Not all plain sailing

Of course there were and still are some issues. As the Transition Curve shows, not everyone is going to follow you and embrace the change you bring. Team members who have been expert in their fields and are happy doing what they do suddenly find they have to learn new things and can feel de-skilled.  By definition, they cannot be an immediate expert at something new that they are asked to do and that can be difficult.

As roles and responsibilities blurred, we found we had to redefine the production process for online content as people became unsure of their roles.

Meanwhile such was the external reputation of the team, we suffered a brain drain to Apple, Amazon and Adidas.

And for me, as the department grew to over 160 people when I took on responsibility for the Picture Editors who edit the video for news and current affairs reports, I had to accept I was going to be more of an enabler and provider of editorial oversight than a practitioner.  Technology was moving so fast, while I had to know and understand it, actually being able to create content myself was going to be a rare occurrence.

Conclusion

Writing this post has helped me see just how far we’ve come as a department in a few short years. It’s certainly not perfect and the challenges we face are ever–changing.  But we have now won over 25 awards across all platforms and the cross-platform vision is embedded in the teams who really enjoy learning from each other and working on projects together.

And, I have a secret weapon.  I enjoy singing pop songs at my desk everyday and of course Carols at Christmas.

Trying not to encourage me to sing is something literally everyone can unite behind.

Bringing teams together was published on SAS Users.

232017
 

Some would say that it's impossible for blind users to see charts and graphs. Those same people might have once said it was impossible for the visually impaired to see the particles that comprise an atom, or galaxies that are billions of light years away. Innovation would prove them wrong [...]

SAS Graphics Accelerator makes charts and graphs visible for blind users was published on SAS Voices by Ed Summers

222017
 

Sometimes SAS programmers ask about how to analyze quantiles with SAS. Common questions include:

  • How can I compute 95% confidence intervals for a median in SAS?
  • How can I test whether the medians of two independent samples are significantly different?
  • How can I repeat the previous analyses with other percentiles, such as the 20th percentile or the 90th percentile?

Historically, PROC UNIVARIATE and PROC NPAR1WAY are two procedures in SAS that analysts used for univariate analysis. PROC UNIVARIATE performs standard parametric tests. In contrast, PROC NPAR1WAY performs nonparametric tests and distribution-free analyses. An internet search reveals many resources that describe how to use UNIVARIATE and NPAR1WAY for analyzing quantiles.

However, there is an alternative way to analyze univariate quantiles: PROC QUANTREG. Although QUANTREG is designed for quantile regression, the same procedure can easily analyze quantiles of univariate data. All you need to do is omit the regressors from the right side of the MODEL statement and the procedure will analyze the "response" variable.

Be aware that the QUANTREG procedure uses an optimization algorithm to perform its analysis. This can sometimes result in different estimates than a traditional computation. For example, if the data set has an even number of observations and the middle values are a and b, one estimate for the median is the average of the two middle values (a+b)/2. The QUANTREG procedure might provide a different estimate, which could be any value in [a, b]. This difference is most noticeable in small samples. (Don't let this bother you too much. There are many definitions for quantile estimates. SAS supports five different definitions for calculating quantiles.)

Confidence intervals for percentiles

I have previously shown how to compute confidence intervals for percentiles in SAS by using PROC UNIVARIATE. The following statements compute the 20th, 50th, and 90th percentiles for the cholesterol levels of 5209 patients in a medical study, along with 95% confidence intervals for the quantiles. The computation is shown twice: first with PROC UNIVARIATE, then with PROC QUANTREG.

/* 1. Use PROC UNIVARIATE to get 95% CIs for 20th, 50th, and 90th pctls */
proc univariate data=Sashelp.Heart noprint;
   var Cholesterol;
   output out=pctl pctlpts=20 50 90 pctlpre=p
          cipctldf=(lowerpre=LCL upperpre=UCL);    /* 12.1 options (SAS 9.3m2) */
run;
 
data QUni;  /* rearrange the statistics into a table */
set pctl;
Quantile = 0.2; Estimate = p20; Lower = LCL20; Upper = UCL20; output;
Quantile = 0.5; Estimate = p50; Lower = LCL50; Upper = UCL50; output;
Quantile = 0.9; Estimate = p90; Lower = LCL90; Upper = UCL90; output;
keep Quantile Estimate Lower Upper;
run;
 
title "UNIVARIATE Results"; 
proc print noobs; run;
 
/**************************************/
/* 2. Alternative: Use PROC QUANTREG! */
ods select none; ods output ParameterEstimates=QReg ;
proc quantreg data=Sashelp.Heart;
   model Cholesterol = / quantile=0.2 0.5 .9;
run;
ods select all;
 
title "QUANTREG Results"; 
proc print noobs;
   var Quantile Estimate LowerCL UpperCL;
run;

The output shows that the confidence intervals (CIs) for the quantiles are similar, although the QUANTREG intervals are slightly wider. Although UNIVARIATE can produce CIs for these data, the situation changes if you add a weight variable. The UNIVARIATE procedure supports estimates for weighted quantiles, but does not produce confidence intervals. However, the QUANTREG procedure can provide CIs even for a weighted analysis.

Test the difference of medians

In general, PROC QUANTREG can compute statistics for quantiles that UNIVARIATE cannot. For example, you can use the ESTIMATE statement in QUANTREG to get a confidence interval for the difference between medians in two independent samples. If the confidence interval does not contain 0, you can conclude that the medians are significantly different.

The adjacent box plot shows the distribution of diastolic blood pressure for male and female patients in a medial study. Reference lines are drawn at the median values for each gender. You might want to estimate the median difference in diastolic blood pressure between male and female patients and compute a confidence interval for the difference. The following call to PROC QUANTREG estimates those quantities:

ods select ParameterEstimates Estimates;
proc quantreg data=Sashelp.Heart;
   class sex;
   model diastolic = sex / quantile=0.5;
   estimate 'Diff in Medians' sex 1 -1 / CL;
run;

The syntax should look familiar to programmers who use PROC GLM to compare the means of groups. However, this computation compares medians of groups. The analysis indicates that female patients have a diastolic blood pressure that is 3 points lower than male patients. The 95% confidence interval for the difference does not include 0, therefore the difference is statistically significant. By changing the value on the QUANTILE= option, you can compare quantiles other than the median. No other SAS procedure provides that level of control over quantile estimation.

Conclusions

PROC QUANTREG provides another tool for the SAS programmer who needs to analyze quantiles. Although QUANTREG was written for quantile regression, the procedure can also analyze univariate samples. You can use the ESTIMATE statement to compare quantiles across groups and to obtain confidence intervals for the parameters.

In general, SAS regression procedures enable you to conduct univariate analyses that are not built into any univariate procedure.

The post Quantile estimates and the difference of medians in SAS appeared first on The DO Loop.

212017
 

What?!?  You mean a period (.) isn't the only SAS numeric missing value? Well, there are 27 others: .A .B, to .Z and ._ (period underscore). Your first question might be: "Why would you need more than one missing value?"  One situation where multiple missing values are useful involves survey data. Suppose [...]

The post The Other 27 SAS Numeric Missing Values appeared first on SAS Learning Post.

212017
 
My paymenet information for this custom URL is expiring and Google told me to update the payment information. That's OK, I will do it. However, by switching to G Suite, Google makes paying this $10.00 so difficult that I decided it is not worthy to continue the business with Google. Its email contains link that won't work for me, taking me to webpages that do not answer the question. All the steps become very bumpy and it is really a bad experience for customers. With this, now I understand why Google is "good at inventing technologies but bad at making products". I will switch to my WordPress sites eventually. This dying out of sas-programming is also chronical with the fading out of SAS from new emerging markets (Surely SAS will still thrive in traditional analytics market for a while).
 Posted by at 7:24 上午
212017
 

The SAS® Output Delivery System provides the ability to generate output in various destination formats (for example, HTML, PDF, and Excel). One of the more recent destinations, ODS Excel, became production in the third maintenance release for SAS 9.4 (TS1M3). This destination enables you to generated native Microsoft Excel formatted files, and it provides the capability to generated worksheets that include graphics, tables, and text. If you generate spreadsheets, then the ODS Excel destination (also known as just the Excel destination) might be just the tool you're looking for to help enhance your worksheets.

This post begins by discussing the following Excel destination options that are useful for enhancing the appearance of worksheets:

  • START_AT=
  • FROZEN_HEADERS= and FROZEN_ROWHEADERS=
  • AUTOFILTER=
  • SHEET_NAME=
  • ROW_REPEAT=
  • EMBEDDED_TITLES=

The discussion also covers techniques for adding images to a worksheet as well as a tip for successfully navigating worksheets. Finally, the discussion offers tips for moving to the use of the Excel destination if you currently use one of the older ODS destinations (for example, the ExcelXP destination) and information about suggested hot fixes.

Using Excel Destination Options to Enhance the Appearance of Your Microsoft Excel Worksheet

There are certain ODS Excel destination options that you could conceivably add to any program that would make it easier for your users to navigate your worksheets.

These options include the following:

  • START_AT= option
  • FROZEN_HEADERS= and FROZEN_ROWHEADERS= options
  • AUTOFILTER= option
  • EMBEDDED_TITLE= option

The following example uses all of the options described above. In this example, filters are added only to the character fields.

ods excel file="c:temp.xlsx" options(start_at="3,3"
frozen_headers="5"
frozen_rowheaders="3"
autofilter="1-5"
sheet_name="Sales Report"
row_repeat="2"
embedded_titles="yes");
 
proc print data=sashelp.orsales; 
title "Sales Report for the Year 1999";
run;
 
ods excel close;

In This Example

  • The START_AT= option enables you to select the placement of the initial table or graph on the worksheet. In Microsoft Excel, by default, a table or graph begins in position A1. However, pinning the table or graph in that position does not always provide the best visual presentation.
  • The FROZEN_ HEADERS= option locks the table header in your table while the FROZEN_ROWHEADERS= option locks row headers. Both of these options lock your headers and row headers so that they remain visible as you scroll through the values in the table rows and columns.
  • The AUTOFILTER= option enables you to add filters to tables headers so that you can filter based on the value of a particular column.
  • The SHEET_NAME= option enables you to add more meaningful text to the worksheet tab.
  • The ROW_REPEAT= option makes your report more readable by repeating the rows that you specify in the option. If this report is ever printed, specifying the rows to repeat, in this case the column headers would allow for easy filtering of the data.
  • The EMBEDDED_TITLE= option specifies that the title that is specified in the TITLE statement should be displayed.

Output

Using the Excel Destination to Add and Update Images

Microsoft Excel is widely known and used for its ability to manipulate numbers. But if you want to go beyond just numbers, you can make your worksheets stand out by adding visual elements such as images and logos.

Graphic images that you generate with ODS Graphics and SAS/GRAPH® software (in SAS 9.4 TS1M3) are easy to add to a worksheet by using the Excel destination. However, the addition and placement of some images (for example, a logo) can take a bit more work. The only fully supported method for adding images other than graphics is to add an image as a background image.

The next sections discuss how you can add various types of images to your worksheet.

Adding Background Images

You can add images to the background of a worksheet by using either the TEMPLATE procedure or cascading style sheets. With PROC TEMPLATE, you add background images by using the BACKGROUNDIMAGE= attribute within the BODY style element. You also must specify the BACKGROUND=_UNDEF attribute to remove the background color. With a cascading style sheet, you use the BACKGROUND-IMAGE style property.

The following example illustrates how to add a background image using PROC TEMPLATE:

proc template; 
define style styles.background;
parent=styles.excel;
class body / background=_undef_
backgroundimage="c:background.jpg.";
end;
run;
 
ods excel file="c:temp.xlsx"
options(embedded_titles="yes" start_at="5,5"
sheet_name= "Sheet1") style=styles.background;
 
proc report data=sashelp.prdsale spanrows;
title "Expense Report Year 2016";
column country product actual predict; 
define country / group;
define product / group;
rbreak after / summarize;
run;
 
ods excel close;

In This Example

  • PROC TEMPLATE uses the BACKGROUNDIMAGE= attribute within the BODY style element of the CLASS statement to include the image.
  • The BACKGROUND=_UNDEF attribute removes the background color.

As you can see in the following output, Excel repeats (or, tiles) images that are used as a background.  Excel repeats the image across the width of the worksheet.

Output

But this method of tiling might not be what you want. For example, you might want your image to cover the entire worksheet. To prevent the background image from being tiled, you can insert the image into an image editor (for example, Microsoft Paint) and enlarge the background image so that it covers the full page. You can also create a canvas (that is, a page) in the image editor and then add your background image to the canvas and save it. The Excel destination does not support transparency, a property in where the background image is visible through an image. However, you can use PROC TEMPLATE to simulate transparency by removing the background colors of the various cells. When you use any of the methods described above, your output includes an image that covers the full page.

The following example uses the PROC TEMPLATE method to create the background image and remove the background colors of the cells:

proc template;
define style styles.background;
parent=styles.excel;
class body / background=_undef_ backgroundimage="C:background_large.jpg";
class header, rowheader, data / color=white 
borderwidth=5pt
bordercolor=white
borderstyle=solid
background=_undef_;
end;
run;
 
ods excel file="c:temp.xlsx" options(embedded_titles="yes"
start_at="5,5"
sheet_name="Sheet1") 
style=styles.background;
 
proc report data=sashelp.prdsale spanrows;
title "Expense Report Year 2016";
column country product actual predict;
define country / group;
define product / group;
rbreak after / summarize;
run;
 
ods excel close;

In This Example

  • First, the image was included in Microsoft Paint to enlarge it.
  • Then, PROC TEMPLATE uses the BACKGROUNDIMAGE= attribute within the BODY style element of the CLASS statement to include the enlarged image.

Output

Adding External Images to the Worksheet

Currently, the Excel destination does not support adding external images on a per-cell basis.

However, you can add external images (for example, a company logo) using either of the following methods:

You can accomplish this task in the following ways:

  • manually add an image using an image editor
  • use the GSLIDE procedure with the GOPTIONS statement
  • use the %ENHANCE macro.

Adding an Image with an Image Editor

Using an image editor such as Microsoft Paint, you can place an image (for example, a logo) wherever you want it on the worksheet. In the following display, the image is brought into the Paint application and moved to the top left of a canvas.

After you save this image, you can include it in an Excel worksheet as a background image using the BACKGROUNDIMAGE= attribute, which displays the logo without repeating it.

proc template; 
define style styles.background_kogo;
parent=styles.excel;
class body / background=_undef_
backgroundimage="c:tempbackground_logo";
end;
run;
 
ods excel file="c:temp.xlsx" style=styles.background_logo;
proc print data=sashelp.class;
run;
ods excel close;

Output

Adding an Image Using the GOPTIONS Statement with the GSLIDE Procedure

You can also use the GOPTIONS statement and PROC GSLIDE procedure with the Excel destination to add a logo on the worksheet. This method requires SAS/GRAPH software to be licensed and installed.

To add a background image to the graph display area of PROC GSLIDE output, specify the IBACK= option in the GOPTIONS statement, as shown in the following example:

ods excel file="c:temp.xlsx" options(sheet_interval="none");
 
goptions iback="c:sas.png" imagestyle=fit vsize=1in hsize=2in;
 
proc gslide;
run;
 
proc report data=sashelp.class;
run;
 
ods excel close;

 

In This Example

  • The GOPTIONS statement with the IBACK= option adds a background image to the graph display area.
  • The IMAGESTYLE=FIT option keeps the image from repeating (tiling).
  • The VSIZE= and HSIZE= options modify the size of the image.
  • The Excel destination suboption SHEET_INTERVAL="NONE" specifies that the image and report output are to be added to the same worksheet.

Output

Adding an Image Using the %EXCEL_ENHANCE Macro

The %EXCEL_ENHANCE is a downloadable macro that enables you to place images on a worksheet in an exact location by using a macro parameter. The macro creates VBScript code that inserts your image in a specified location on the worksheet during post-processing.

The following example uses the %EXCEL_ENHANCE macro to add an image to a workbook.

Note: This method is limited to Microsoft Windows operating environments.

%include "C:excel_macro.sas";
%excel_enhance(open_workbook=c:temp.xlsx, 
insert_image=%str(c:SAS.png#sheet1!a1,
c:canada.jpg#sheet1!b5,
c:germany.jpg#sheet1!b10,
c:usa.jpg#sheet1!b15),
create_workbook=c:temp_update.xlsx,
file_format=xlsx);

In This Example

  • The %INCLUDE statement includes the %EXCEL_ENHANCE macro into your program.
  • The %EXCEL_ENHANCE macro uses the INSERT_IMAGE= parameter to insert an image into the worksheet at a specified location. You can also specify multiple images, but they must be separated by commas.

The INSERT_IMAGE= option uses the following syntax in the %STR macro function to pass the image.

image-location #sheet-name ! sheet-position
  • The OPEN_WORKBOOK= parameter specifies the location of the workbook in which you want to add an image.
  • The CREATE_WORKBOOK= parameter creates a new workbook that includes your changes.
  • The FILE_FORMAT= parameter enables you to specify the extension or format for the files that are created.

Output

Navigating a Microsoft Excel Workbook

When you generate an Excel workbook with the Excel destination, the best way to navigate the workbook is by creating a table of contents. You can create a table of contents by using the Excel destination's CONTENTS= option. You can also use the PROC ODSLABEL statement to modify the table-of-contents text that is generated by the procedure. In the following example, that text (The Print Procedure) is generated by the PRINT procedure.

ods excel file="c:temp.xls" options(embedded_titles="yes"
 contents="yes");
 
ods proclabel= "Detail Report of Males";
 
proc print data=sashelp.class(where=(sex="M"));
title link="#'The Table of Contents'!a1"  "Return to TOC";
run;
 
ods proclabel= "Detail Report of Females";
 
proc print data=sashelp.class(where=(sex="F"));
title link="#'The Table of Contents!a1'"  "Return to TOC";
run;
 
ods excel close;

 

In This Example

  • The CONTENTS= option is included in the ODS EXCEL statement to create a table of contents. You can also use the INDEX= suboption (not shown in the example code) to generate an abbreviated output. These options create the first worksheet within a workbook and include an entry to each element that is generated in the workbook.
  • The ODS PROCLABEL statement is used to modify the table-of-contents text that is generated by the procedure name. In this example, the text The Print Procedure (generated by the two PROC PRINT steps) is modified to Detail Report of Males and Detail Report of Females, respectively.
  • You can also modify the secondary link by using the CONTENTS= option in the procedure statements for the PRINT, REPORT, and TABULATE procedures.
  • The LINK= option in the TITLE statement adds a link that returns you to Table of Contents navigation page. You can also use this option in a FOOTNOTE statement. The argument that you specify for the LINK= option is the sheet name for the Table of Contents page. You can also add a link by using the Microsoft Excel hyperlink function in the ODS TEXT= statement using the Excel Hyperlink function.

Output

The output below shows the Table of Contents navigation page.

The next output shows a page in the report that contains the Return to TOC link.

Using the ODS Excel Destination instead of Older Destinations

Currently, you might be using older destinations (for example, the MSOffice2K or the ExcelXP tagsets).  If you decide to move to the ODS Excel destination, you'll notice differences related to style, options, and wrapping between it and other destinations.

  • One difference is that the Excel destination uses the Excel style, which looks similar to the HTMLBlue style. Other destinations use different styles (for example, the ExcelXP tagset uses styles.default.
  • Certain options that are valid with the ExcelXP tagset are not valid in the Excel destination.
  • Another difference that you'll notice right away is how the text is wrapped by the Excel destination. By default, the Excel destination uses an algorithm to try to wrap columns in order to prevent overly wide columns. When text wraps, a hard return is added automatically to the cell (similar to when you either press Alt+ Enter from the keyboard under Windows or you submit a carriage-return line feed [CRLF] character). You can prevent the Excel destination from adding this hard return in wrapping by specifying a width value that is large enough so that text does not wrap. You can also use the Excel destination's new FLOW= suboption, which is available in the fourth maintenance release for SAS 9.4 (TS1M4). This option contains the parameters TABLES, ROWHEADERS, DATA, TEXT, and a range (for example, A1:E20). When you specify the parameter TABLES, that automatically includes the HEADERS, ROWHEADERS, and DATA parameters.

The following example demonstrates how to prevent the Excel destination from automatically generating a hard return for wrapped text in SAS 9.4 TS1M4.

data one;
var1="Product A in Sports";
var2="Product B in Casual";
label var1="Product Group for All Brands in Region 1";
label var2="Product Group for All Brands in Region 2";
run;
 
ods excel file="c:temp.xlsx" options(flow="tables");
 
proc print data=one label;
run;
 
ods excel close;

In This Example

  • The first table shown in the output below is created by default. As a result, the header wraps in the formula bar where the CRLF character is added.
  • The second table in the output is generated with the FLOW="TABLES" suboption (in the ODS EXCEL statement) to prevent the destination from adding the CRLF character to table headers, row headers, and data cells. When you add this option, Microsoft Excel text wrapping is turned on, by default.

Output

Table that is created by default:

Table that is created by including the FLOW="TABLES" suboption in the ODS EXCEL statement:

Hot Fixes for the Excel Destination

If you run SAS 9.4 TS1M3 and you use the ODS Excel destination, see the following SAS Notes for pertinent hot fixes that you should apply:

  • SAS 56878, "The ODS destination for Excel generates grouped worksheets when multiple sheets are produced"
  • SAS Note 57088, "The error 'applied buffer too small for requested data' might be generated when you use the ODS destination for Excel"

Resources

Bessler, Roy. 2015. "The New SAS ODS Excel Destination: A User Review and Demonstration."
Proceedings of the Wisconsin, Illinois SAS Users Group. Milwaukee, WI.

Huff, Gina. 2016. "An 'Excel'lent Journey: Exploring the New ODS EXCEL Statement."
Proceedings of the SAS Global Forum 2016 Conference. Cary, NC: SAS Institute Inc.

Parker, Chevell. 2016. "A Ringside Seat: The ODS Excel Destination versus the ODS ExcelXP Tagset."
Proceedings of the SAS Global Forum 2016 Conference. Cary, NC: SAS Institute Inc.

Tips for using the ODS Excel Destination was published on SAS Users.