ods

9月 182015
 

ProblemSolversMany users of ODS Graphics will be excited to learn that, beginning with SAS® 9.4 TS1M3, SG procedures are able to recognize formatted values that contain Unicode values. One benefit of this is that it allows you to include, without using annotation, special symbols such as greater-than-or-equal-to signs or Greek characters in your axis tick-mark values. You have also asked for syntax which can more easily assign colors to your graphs without having to define a style using PROC TEMPLATE. In this blog, we take a closer look at syntax that you can use to accomplish both of these tasks for PROC SGPLOT. We'll also discuss syntax that produces similar results with the Graph Template Language (GTL).

Recognizing Formatted Values That Contain Unicode

For example purposes, I want to produce a box plot that looks at the levels of cholesterol across patient ages. My program, below, uses a data set called sashelp.heart, which contains values for each patient’s age at the start.


proc sort data=sashelp.heart out=heart;
        by ageatstart;
      run;
proc sgplot data=heart ;
Title1 "Cholesterol Level by Age Range";
   vbox cholesterol / category=AgeAtStart ;
run;

By default, when I submit the program, I get a box for each value of AgeAtStart that is in the data set sashelp.heart.

 

Sept1

Wow, that’s a lot of boxes. This chart is crowded and hard to read.

Now, let’s group the boxes into age ranges using a format I created called AGEFMT. Each formatted value should display the lower and upper ages of the range separated by a greater-than-or-equal-to sign. Note that to display the greater-than-or-equal-to sign, I need to use the Unicode value 2264. And because ODS Graphics does not recognize the escapechar, I use (*ESC*) in my format definition.

 

proc format;
   value agefmt
   20-30 = "20 (*ESC*){unicode '2264'x} 30"
   30-40 = "30 (*ESC*){unicode '2264'x} 40"
   40-50 = "40 (*ESC*){unicode '2264'x} 50"
   50-60 = "50 (*ESC*){unicode '2264'x} 60"
   60-70 = "60 (*ESC*){unicode '2264'x} 70"
   ;
run;
proc sgplot data=heart ;
Title1 "Cholesterol Level by Age Range";
   vbox cholesterol / category=AgeAtStart ;
format ageatstart agefmt.;
run;

If I use SAS 9.4 TS1M2 or earlier to submit my program, the results look like the following. Notice that the Unicode values in the PROC FORMAT do not resolve in the output produced by PROC SGPLOT like they do for the Base SAS® procedures. Prior to SAS 9.4 TS1M3, I would need to use annotation in order to display axis values that contain Unicode values. (Sample 48123 illustrates the syntax.)

Sept2

Fortunately, these issues disappear with SAS 9.4 TS1M3. When I submit my modified program, my results look much better:

Sept3

An Easier Way to Assign Colors to Your Graphs

I want each box to be a different color, so I will add the option GROUP=AgeAtStart to my VBOX statement.

 

proc format;
   value agefmt
   20-30 = "20 (*ESC*){unicode '2264'x} 30"
   30-40 = "30 (*ESC*){unicode '2264'x} 40"
   40-50 = "40 (*ESC*){unicode '2264'x} 50"
   50-60 = "50 (*ESC*){unicode '2264'x} 60"
   60-70 = "60 (*ESC*){unicode '2264'x} 70"
   ;
run;

proc sgplot data=heart ;
Title1 "Cholesterol Level by Age Range";
   vbox cholesterol / category=AgeAtStart group=AgeAtStart ;
format ageatstart agefmt.;
run;

In addition to each box being represented with a different color, a default legend is also displayed.

Sept4

I am going to suppress the legend by adding the option NOAUTOLEGEND to the PROC SGPLOT statement. I also want to change the default colors for the boxes. Prior to SAS 9.4 TS1M0, I would have had to do this through an attribute map definition or a style definition like this one:

 

proc template;
define style colors;
parent=styles.htmlblue;
class graphdata1 / color=red;
class graphdata2 / color=green;
class graphdata3 / color=purple;
class graphdata4 / color=orange;
class graphdata5 / color=cyan;
end;

Beginning in SAS 9.4 TS1M0, I assign the colors with the DATACOLORS option in the STYLEATTRS statement in PROC SGPLOT. In addition to changing the fill color of the boxes, I change the color of my graph background and the plot wall area with the BACKCOLOR and WALLCOLOR options, respectively, in the STYLEATTRS statement. Here is the final code:

 

proc format;
   value agefmt
   20-30 = "20 (*ESC*){unicode '2264'x} 30"
   30-40 = "30 (*ESC*){unicode '2264'x} 40"
   40-50 = "40 (*ESC*){unicode '2264'x} 50"
   50-60 = "50 (*ESC*){unicode '2264'x} 60"
   60-70 = "60 (*ESC*){unicode '2264'x} 70"
   ;
run;
proc sgplot data=sashelp.heart noautolegend;
Title1 "Cholesterol Level by Age Range";
   styleattrs datacolors=(red green purple orange cyan) backcolor=vpav wallcolor=pwh;
   vbox cholesterol / category=AgeAtStart group=ageatstart ;
format ageatstart agefmt.;
run;

This is the final graphics output:

Sept5

Using GTL to Create the Same Graph

The syntax below creates this graph using GTL. The color-fill assignments are made using the DATACOLORS option in the BEGINGRAPH statement. The BACKGROUNDCOLOR option in the BEGINGRAPH statement specifies the background color, and the WALLCOLOR option in the LAYOUT OVERLAY statement defines the color of the axis wall.

 

proc template;
 define statgraph boxes;
 begingraph / datacolors=(red green purple orange cyan) backgroundcolor=vpav;
    layout overlay / wallcolor=pwh;
     boxplot x=AgeAtStart y=cholesterol / group=AgeAtStart;
  endlayout;
 endgraph;
 end;

proc sgrender data=sashelp.heart template=boxes;
Title1 "Cholesterol Level by Age Range";
format AgeAtStart agefmt.;
run;
tags: Graph Template Language (GTL), ods, Problem Solvers, PROC SGPLOT, SAS Programmers

Specifying Unicode values and colors easier with the PROC SGPLOT and GTL was published on SAS Users.

6月 052015
 

You are all familiar with the traditional SAS Output Delivery System (ODS) destinations such as LISTING, HTML, PDF, or POWERPOINT that use “destination” in a sense of type of the output file. However, in this blog post, I am going to use term “destination” in even more traditional sense – as the place to which someone is going or to which something is being delivered.

Marrying ODS destination with Google map destination

To be precise, I am going to use term “destination” for information delivery to a specific location on Google map. We will produce SAS ODS output and deliver it to particular locations on a Google map.

Since Google maps exist in the web page environments that are essentially HTML, the best suited ODS destination for such a “marriage” will be ODS HTML.

With this technique, we are not limited by mere texts or images placed on Google maps. We can place on Google map output of any SAS procedure – tables and graphs.

Here is an interactive example of delivering ODS output to a Google map destination/location (the data itself is totally fictitious and serves the purpose of illustrating the technique only). Please take a minute to explore this Google map interaction before reading further.

Sample of Google map with embedded SAS ODS output

How it is done

Click to view and download the full sample SAS code. In this code, I capitalized on my prior posts on Google map with SAS. In particular, I used the idea of creating mini-pages for each location described in Weather forecasting with SAS-generated Google maps.

The main difference here is that the HTML mini-pages (place1.html, place2.html, etc.)  for each geographical location were created using SAS ODS HTML destination, which illustrated by the following SAS macro:

%macro create_sas_outputs;

  %let dsid = %sysfunc(open(places));
  %let num  = %sysfunc(attrn(&dsid,nlobs));
  %let rc   = %sysfunc(close(&dsid));

  filename odsout "&proj_pathinfopages";

  %do j=1 %to #

    data _null_;
      p = &j;
      set places point=p;
      call symput('placename',place);
      stop;
    run;

    ods html path=odsout file="place&j..html" style=styles.seaside;

    goptions reset=all device=actximg colors=() htext=9pt hsize=3in vsize=1.5in;

    title1  bold h=10pt color=cx3872ac "SAS user levels in &placename";
    axis1 label=none;
    axis2 label=none value=none minor=none major=none;

    proc gchart data=sasusers(where = (place eq "&placename"));
         vbar saslevel /
        sumvar = count
        width = 10
        outside = sum
        raxis = axis2
        maxis = axis1
        cframe = white nozero discrete 
        ;
      format count comma15. saslevel levelf.;
    run;
    quit;

    ods html close;

  %end;

%mend create_sas_outputs;

In this macro, for simplicity of the code sample I used PROC GCHART, but it can be any SAS procedure (PROC REPORT, PROC TABULATE - you name it) or a combination of several SAS procedures.

The rest of the technique is based on creating Google map InfoWindows that reference these HTML mini-pages via <iframe> tag as shown in this code snippet:

put 'var info' i '= ''<iframe style="width:320px;height:215px" src="infopages/place' i +(-1) '.html">'';' /

I hope this post will serve as yet another illustration of the power of SAS as a tool for information delivery where it’s needed and when it’s needed.

What are your thoughts on this?

tags: Google maps, ods, SAS Professional Services, SAS Programmers

The post SAS ODS destination - Google maps appeared first on SAS Users.

5月 212015
 

If you have programmed with SAS in the last 15 years, you have probably had a reason to share your SAS results in PDF format. The ODS PDF destination, much like a well-designed car, has evolved over the years, offering progressively nicer features like security, enhanced image formatting and embedded fonts. I think you’re going to like what SAS 9.4 has to offer!

Let’s discuss three key SAS 9.4 improvements to this beloved, reliable and top rated ODS destination:

  • Pearl default style
  • ODS LAYOUT statement
  • ODSLIST and ODSTEXT procedures

ODS PDF’s new default style: Not just a pretty paint color.

Just like car manufacturers, the ODS developers have improved the “look and feel” of the PDF destination in SAS 9.4 by updating the default style. Styles.Pearl, the new default style for ODS PDF and ODS PRINTER,  is designed with a more modern feel. The headers are no longer shaded, the borders are lighter and we think you’ll agree the results are much more appealing. Here’s a screen shot showing sample PROC PRINT results comparing the new default style, styles.Pearl, with the earlier default, styles.Printer:

Illustration showing differences in SAS 9.4 default ODS Styles.PEARL and previous default Styles.PRINTER

ODS LAYOUT: Production status with robust documentation

The ODS LAYOUT feature, which allows the precise placement of SAS output on a PDF page, was introduced in SAS 9.2. However, it held so much power and required so many feature improvements, it was released in preproduction status. Since that time the SAS programming community has offered feedback on its performance and features, leading to the SAS 9.4 release where the destination is offered with two distinct models: GRIDDED and ABSOLUTE, both of which offer robust “owner’s manuals”.

We think the ABSOLUTE model will be the most popular. You can use it to place tables and images side by side, stack them top to bottom and even overlay output in your PDF files, and works to create output placed on a single PDF page.  The GRIDDED model is more useful when using BY-group data and/or creating multi page output.

The ABSOLUTE LAYOUT syntax uses a pair of ODS LAYOUT statements (ODS LAYOUT START and ODS LAYOUT END) to define the area on the page to which you will write ODS REGIONs.  ODS REGION statements define the location and size of regions to which you will write tables, text or images. Both statements use the X= argument to define the horizontal “start” position, and Y= to define the vertical start position of your output. If you do not use either of these, the output starts in the upper left corner (0,0). Height= and Width= arguments can be used on both statements to control the size of the layouts and regions.

Here is sample code for defining a sleek layout page within your ODS PDF destination:

  • Create a PDF file with FILE=, turn off the bookmark/table of contents with NOTOC, and with NOGTITLE make all Graph titles part of the PDF file, instead of embedding them in the Graph images.
    ods pdf file="file.pdf" notoc nogtitle ;
  • Define a LAYOUT that is 7.25 inches high and 10.5 inches wide.  The keyword ABSOLUTE is not necessary as it is implied if neither the COLUMNS= nor the ROWS= option is used. Previously we have set the system option ORIENTATION= to LANDSCAPE on an OPTIONS statement and set a Title and Footnote, whose text will be placed outside the layout.
    ods layout start height=7.25in width=10.5in ;
  • Define the first REGION. The region is placed inside my layout, starting .75 inch from my top margin (y=.75in) and one inch from my left margin (x=1in). This region is allocated 3.5inches of space across my page (width=3.5in).
    ods region x=1in y=.75in width=3.5in ;

The ODS LAYOUT and REGION statements used above are part of Sample 55808.  We’ll use the full code to generate the following output from SAS 9.4, and I’ll explain how to assemble all the pieces using other new features in SAS 9.4:

SAS 9.4 ODS output using ODS LAYOUT statement

PROC ODSTEXT: easily insert paragraphs of text

The ODSTEXT procedure helps us format paragraph text that would previously have been written with DATA _null_ / FILE PRINT logic or ODS <destination> TEXT= syntax. However, neither of these methods allow for elegant style control and line spacing, nor do either of those strategies integrate well with the table of contents.  Now using PROC ODSTEXT, we can format paragraphs of text (or just single words if preferred!), with ease.

Here’s the first PROC ODSTEXT code used in our screen shot shown above:

  
proc odstext;
    p "The ODSTEXT procedure offers a powerful tool for inserting paragraph style text into your ODS destinations. "  / style=systemtitle;
    p  "The ODSTEXT procedure allows us to: format text with style control offered with the statement option style=,  use data sets, and add bullets."  / style=systemtitle;
    p '  ';
    p "A close sibling is PROC ODSLIST, used to generate the following bulleted list of improved ODS PDF features:" /  style=header;
run;

Here are the results:

PROC ODSTEXT output showing three sizes and

PROC ODSLIST: easily create bulleted lists

Closely related to the ODSTEXT procedure is PROC ODSLIST, which allows us to create nicely formatted, indented and bulleted lists from a SAS dataset. Here’s an example:

 proc odslist data=features;
   item feature /style={bullet="disc"};
   end;
 run;

Here are the results:

PROC ODSLIST output
There’s an added bonus in SAS 9.4: the ODSLIST and ODSTEXT procedures can be used in ANY of the non-LISTING ODS destinations.  Try them out in ODS POWERPOINT and ODS RTF too!

Adding the bar chart to our layout

The image in our PDF file is created with the PROC SGPLOT and highlights a feature new in SAS 9.4, the SYMBOLCHAR statement.  The data and program are described in Sample 54315 and a recent Problem Solvers blog. The key to sizing and inserting the output in the upper right corner of our PDF file is specifying the following ODS REGION statement and the ODS GRAPHICS statement height= option before the PROC SGPLOT code.

ods region x=4in y=.5in ; 
ods graphics on / reset noborder height=3.5in;

Adding the table to the PDF

The bottom table is created by PROC REPORT along with some “power assist” from PROCs ODSTEST and FORMAT. This region is defined only with a Y= specification as I want REPORT’s table to take up the width of the entire region. The REPORT table will be centered by default, but ODSTEXT= will not, hence the j=c style override.

ods region y=4in; 

proc format;
value myfmt low-40="^{style [foreground=red] ^{unicode 2193} "
           40-high="^{style [foreground=green] ^{unicode 2191} ";
run;

proc odstext ;
   p " ";
   p "A subset of SASHELP.CARS where MPG_HIGHWAY is greater than 40 " / style={font=(", Albany",10pt,bold) just=c};
 run;

proc report data=sashelp.cars(where=(mpg_highway gt 40 and cylinders eq 4)) spanrows
style(report)={posttext="^{style [font_weight=bold] PROC FORMAT uses inline formatting and the Unicode style function to differentiate models based on the combined MPG}" };
col make model mpg_highway mpg_city avg type enginesize horsepower drivetrain;
define make / order;
define avg  / computed "Combined MPG" format=8. style(column)={posttext=myfmt.};
compute avg;
   avg=sum(mpg_highway.sum,mpg_city.sum)/2;
endcomp;
run;

Here are the results:

Using ODS LAYOUT feature to create and place a PROC REPORT table
We “put the brakes on” by closing the LAYOUT and the PDF destination:

ods layout end;
ods pdf close;

I hope you have enjoyed “kicking the tires” on my favorite destination. Are you excited about getting this new and updated model? If so, give your SAS dealership (ahem, representative) a call and upgrade today!

Please read the fine print:

If you have been an intrepid ODS LAYOUT coder prior to SAS 9.4, you are likely to see different results from your ODS LAYOUT/REGION statements when you move to SAS 9.4. If using the legacy style, styles.printer, does not correct the results, use Scott Huntley’s paper “An Insider’s Guide to ODS LAYOUT Using SAS® 9.4” . It is an excellent resource for coders moving ODS LAYOUT code from previous releases.

If you are moving from a SAS release prior to version 9.2, Scott and I wrote this paper “Getting the Right Report (Again): Your Compatibility Guide for ODS PDF 9.2” to discuss changes that took place for ODS PDF in that version.

Scott and Woody Middleton introduce you to the new features for ODS PDF in SAS 9.3 in "A Different Point of View with ODS PDF in SAS®9.3".

SAS has provided printable tip sheets for ODS PDF, ODS LAYOUT, PROC ODSTEXT, PROC ODSLIST, ODS GRAPHICS and more.

tags: ods, Problem Solvers, reporting, SAS 9.4, SAS Programmers

The post ODS PDF destination in SAS 9.4: Come take a look under the hood! appeared first on SAS Users.

12月 192014
 

SAS Technical Support Problem SolversIf you haven’t tried them for your web applications and other graphics needs, you’ll want to read further!

Scalable Vector Graphics (SVG) output is vector graphics output you can display with most (if not all) modern web browsers. Because SVG graphic output is scalable, you can zoom in on the graphics output without losing resolution. Unlike bit-mapped images such as PNG or GIF output, they can be resized or transformed without compromising the clarity, eliminating the need to produce multiple versions of the same image! There are other advantages for using Scalable Vector Graphics like their ability to zoom in to view details, their smaller output file size and their usefulness for producing graphics for a range of display sizes and types.

Which SAS products offer SVG graphics?

The SVG family of device drivers has shipped as part of the SAS/GRAPH product since the SAS 9.2 release. Note that you can only use these SVG device drivers with traditional SAS/GRAPH procedures such as PROC GPLOT and PROC GCHART.

Starting with SAS 9.3 version of the Base product, you can also create SVG output with the SAS SG procedures such as SGPLOT and SGPANEL as well as with graphics output created with ODS Graphics. In SAS 9.4, you can also use Scalable Vector Graphics to produce animations.

Typically, when you create SVG graphics, you will want to create the output in one of these ways:

  • a standalone SVG file with a file extension of .svg
  • an HTML output file using the ODS HTML statement
  • an HTML5 output file in SAS 9.4

The output method you choose depends on your application. If you’re creating standalone SVG files, you can use that SVG file in some other document and make reference to it in another HTML page. For example, a common application for this would be creating logos in SVG that can be sized to any space. If you are using SAS 9.4, the HTML5 method is the best when creating an HTML document because the SVG can be embedded directly and there are no additional files to be moved.

In this blog post I’ll show you how to produce each one of these output types using the Base Product or SAS/GRAPH. I’ve also included a list of sample SAS/GRAPH animations that you can try.

Creating Scalable Vector Graphics with Base SAS

In SAS 9.3 and SAS 9.4, you can specify Scalable Vector Graphics output by specifying the OUTPUTFMT=SVG option on the ODS Graphics statement before the procedure step, such as:

ods graphics on / outputfmt=svg; 

The examples in this sections use the sashelp.cars data set shipped with the SAS 9.3 and SAS 9.4 Base product to produce a bubble plot.

svg_sgplot

Stand-alone SVG file. The following sample code uses PROC SGPLOT to write a standalone SVG file with the name sastest.svg to the C:temp directory when running on the Windows operating system:

    ods _all_ close; 
    ods listing gpath='c:temp';

    ods graphics / reset=all outputfmt=svg imagename='sastest'; 
 
    title1 'Plot of MPG City versus Horsepower';  
    proc sgplot data=sashelp.cars; 
      bubble x=horsepower y=mpg_city size=cylinders;
    run;

HTML file. This code uses the same PROC SGPLOT code to write a SVG file along with a corresponding HTML file to C:temp when running on the Windows operating system:

    ods _all_ close; 
    ods html path='c:temp' (url=none) file='svg.html'; 

    ods graphics / outputfmt=svg; 

    title1 'Plot of MPG City versus Horsepower';  
    proc sgplot data=sashelp.cars; 
      bubble x=horsepower y=mpg_city size=cylinders;
    run;

    ods html close; 
    ods listing; 

HTML 5 file. With SAS 9.4 only, you can use PROC SGPLOT with the ODS HTML5 statement to embed the SVG output in an HTML file. Note that with the code below, the SVG output is embedded inside the HTML output via the use of the svg_mode='inline' option on the ODS HTML5 statement.

    ods _all_ close; 
    ods html5 path='c:temp' (url=none) file='svg.html'
                options(svg_mode='inline');

    ods graphics / outputfmt=svg; 

    title1 'Plot of MPG City versus Horsepower';  
    proc sgplot data=sashelp.cars; 
      bubble x=horsepower y=mpg_city size=cylinders;
    run;

    ods html5 close; 
    ods listing; 

Creating Scalable Vector Graphics with SAS/GRAPH

The examples in this sections use PROC GPLOT and the sashelp.class data set to produce a linear plot of weight versus height.

svg_gplot

Stand-alone SVG file. Here is sample SAS code that uses PROC GPLOT to write a standalone SVG file with the name sastest.svg to the Temp directory on your C: drive when running on the Windows operating system:

    ods _all_ close; 
    ods listing;

    filename grafout 'c:tempsastest.svg'; 

    goptions reset=all device=svg gsfname=grafout;  

    symbol1 i=none v=dot c=black h=1.5;
    axis1 minor=none;  
    title1 'Plot of Weight versus Height';
    proc gplot data=sashelp.class;
      plot weight*height / haxis=axis1 vaxis=axis1;
    run;
    quit;  

HTML file. Here’s how to write the same output to a SVG file along with a corresponding HTML file:

    ods _all_ close; 
    ods html path='c:temp' (url=none) file='svg.html';  

    goptions reset=all device=svg;  

    symbol1 i=none v=dot c=black h=1.5;
    axis1 minor=none;  
    title1 'Plot of Weight versus Height';
    proc gplot data=sashelp.class;
      plot weight*height / haxis=axis1 vaxis=axis1;
    run;
    quit;  

    ods html close; 
    ods listing;

HTML 5 file. With SAS 9.4 only, the following sample code uses PROC GPLOT together with the ODS HTML5 statement to embed the SVG output in the resulting HTML file. Note that with the code below, the SVG output is embedded inside the HTML output via the use of the svg_mode='inline' option on the ODS HTML5 statement.

    ods _all_ close; 
    ods html5 path='c:temp' (url=none) file='svg.html'
               options(svg_mode='inline');   

    goptions reset=all device=svg;  

    symbol1 i=none v=dot c=black h=1.5;
    axis1 minor=none;  
    title1 'Plot of Weight versus Height';
    proc gplot data=sashelp.class;
      plot weight*height / haxis=axis1 vaxis=axis1;
    run;
    quit;  

    ods html5 close; 
    ods listing;  

Using Scalable Vector Graphics for animation in SAS/GRAPH

Beginning with SAS 9.4, you can create animated graphs for the web using the SVG device driver together with new options available on the OPTIONS statement. Here are links to sample programs on support.sas.com that demonstrate how to create animated graphs for the web using the SAS 9.4 SVG device driver:

tags: base sas, HTML5, ods, SAS Problem Solvers, sas/graph, Scalable Vector Graphics
10月 292014
 

The W3C has announced that HTML 5 is now an official recommendation. This designation is, in some ways, just a formality, but it is important for SAS. It paves the way for the HTML destination of ODS to deliver results in HTML 5 format in future SAS releases. For ODS purposes, HTML 5 is only slightly different from HTML 4, so you may not notice the difference in the way ODS codes output tables. An advantage of HTML 5, though, is that there is more agreement among web browsers about the way HTML 5 documents should be displayed. This particularly affects spacing details such as cell padding, the small blank area between the data in a table cell and the cell border. In previous HTML versions, browsers did not agree on whether this spacing should be counted when computing the cell width and height. The result is that tables can look noticeably different from one browser to another.

With greater consistency in spacing in HTML 5, you can have more confidence that output files will have a similar look and feel for everyone who looks at the output, regardless of the browser they are using. This in turn should allow style sheets to be more precise, specific, and efficient in the visual design of HTML output tables from SAS, matching the precision that you can already expect in paginated destinations such as PDF.

The HTML destination is one of the most important in ODS, since it is often the default destination. HTML 5 as the default could come with some style sheet adjustments. These are things programmers can look for in the next SAS release.

9月 052014
 

The codes to push a dataset to Excel (technically XML):

ods tagsets.excelxp file="test.xls";
 
*#1;
ods tagsets.excelxp options( sheet_name=’test 1′);
proc print data=sashelp.class noobs;
    var height / style={tagattr=’format:text’} style(column)={cellwidth=.5 in};
run;

*#2; 
ods tagsets.excelxp options( sheet_name=’test 2′);
proc print data=sashelp.class noobs;
    var height / style(column)={cellwidth=.5 in} style={tagattr=’format:text’} ;
run;
 
ods tagsets.excelxp close;

The output of #1:SAS_ODS_options1

#2:SAS_ODS_options2

The difference among the two pieces of code, #1 and #2 is trivial, as far as observed: only the position of the ODS style options was swapped. The weird thing is that they produced different outputs with different column width and cell format.

The answer: I don’t know. I threw this question to SAS-L and SAS tech and we might come back to this question days later.

9月 022014
 

Using PROC SGPLOT for Quick High-Quality GraphsSoon I will travel to San Jose for the Western Users of SAS Software 2014 Educational Forum and Conference.  I’m looking forward to doing a hands-on workshop on one of my favorite topics, ODS Graphics, specifically the PROCs SGPLOT and SGPANEL.  Here is the abstract:

New with SAS 9.2, ODS Graphics introduced a whole new way of generating graphs using SAS.  With just a few lines of code, you can create a wide variety of high-quality graphs.  This workshop shows how to produce several types of graphs using PROC SGPLOT, and how to create paneled graphs by converting PROC SGPLOT to PROC SGPANEL.  This workshop also shows how to send your graphs to different ODS destinations, how to apply ODS styles to your graphs, and how to specify properties of graphs, such as format, name, height, and width.

If you are going to the conference, I hope you will attend my workshop Thursday, September 4, 2014 4:00-6:00pm. If not, then you can download the paper, step-by-step handout, and syntax reference tables.


8月 302014
 

The ODS ExcelXP tagset has served us well over the years. It provides a reliable method to get formatted SAS output into Microsoft Excel workbooks, where the business world seems to like to live. And it's available in Base SAS, which means that you don't need SAS/ACCESS to PC Files and any extra setup to begin using it.

In SAS 9.4 Maintenance 1, ODS EXCEL was introduced as an experimental feature. Even though it still has the "experimental" label in the recently released SAS 9.4M2, I've found it to be a useful addition to the many ways I can create Excel content from SAS. ODS EXCEL offers a couple of big advantages over ExcelXP:

ODS EXCEL produces a native XLSX file. Users of the ExcelXP tagset know that Excel complains about the file format as you open it. And the XML Spreadsheet format that is uses is uncompressed, resulting in potentially very large files.

ODS EXCEL supports SAS graphics in the output. ExcelXP users have come up with creative methods to insert graphs "after the fact", but it's not as convenient as a "once and done" SAS program. With ODS EXCEL, graphics from SAS procedures are automatically included in output.

Syntax-wise, ODS EXCEL is similar to ODS TAGSETS.ExcelXP. Chevell Parker shares many of those details in his SAS Global Forum 2014 paper; in absence of official doc for this experimental feature, Chevell's paper is essential.

Let's dive into an example. The following program looks similar to how you might use the ExcelXP tagset, but can you see the key differences?

ods excel file="c:projectsoutputexample.xlsx" 
 /* will apply an appearance style */
 style=pearl
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* name the sheet tab */
  sheet_name="CARS summary"
 );
 
/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Cars Summary and Histogram";
 
/* tabular output */
proc means data=sashelp.cars;
var msrp invoice;
run;
 
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
run;
 
ods excel close;

Note how I've targeted an XLSX file directly, instead of going through an XML intermediary file to placate Excel with the file format. And I included a graph with no problem. Like other ODS output destinations, I can add apply styles and special formatting as needed. Here's a screen shot of the resulting spreadsheet:

odsexcel

I have a few additional observations from playing with this feature (did I say it was experimental?):

  • ODS EXCEL doesn't perform well with large detailed output. That is, if I use ODS EXCEL and PROC PRINT a data set with lots of columns and many thousands of rows, it's going to be slow -- and might hit a wall with memory usage. For that use case, PROC EXPORT DBMS=XLSX works better (though that requires SAS/ACCESS to PC FILES).
  • ODS EXCEL overwrites any existing Excel file that you target. That means that you aren't going to use this method to poke new values into an existing spreadsheet, or add sheets to an existing workbook. Compare that to PROC EXPORT DBMS=XLSX, which allows you to update an existing workbook by targeting just one sheet.
  • My example code works perfectly on the SAS University Edition! Simply change the destination file to be a location that SAS can write to -- for example, file="/folders/myfolders/example.xlsx".

If you want to learn more about the new ODS destinations in SAS 9.4 (which include HTML5, EPUB, POWERPOINT, and EXCEL), watch my interview and demo with David Kelley, R&D manager for ODS at SAS:

tags: excel, excel 2007, ods, ODS EXCEL, SAS 9.4, SAS University Edition
4月 182014
 
In writing the second edition of SAS Programming in the Pharmaceutical Industry, I knew that I wanted to replace the device-driven SAS/GRAPH figures with the new ODS template-driven graphics procedures. The latest developments in SAS graphics involve the template-driven procedures and tools found in SAS ODS graphics (i.e., ODS Graphics […]
4月 162014
 

When you run a program or task in SAS Enterprise Guide, the application wraps your job in an "ODS sandwich", the colloquial term we use for the ODS statements necessary to create output that can be viewed in your project.

That's convenient for exploring and refining your program, but at some point you might want to use your own ODS statements and options to control the format and appearance of the output. You might find that the default ODS sandwich that SAS Enterprise Guide generates can get in your way.

For example, suppose that you want to create a PDF output with landscape orientation and Legal paper size. The default SAS Enterprise Guide options might be in conflict with some of the options you need. The best approach? Squelch the "automatic" options, and code up your own exactly how you need them. Here's how.

First, change the properties of your SAS program.. Right-click on the program node in your flow and select Properties, or click the Properties icon at the top of the editor window:

codebar

In the Properties window, select the Results tab. Then select the Customize results formats [...] option, and uncheck all of the built-in formats.

resultsopts
Next, add the ODS statements and options necessary to produce the results that you want. For example, if you want a PDF file that has a particular format, use something like this:

options orientation=landscape papersize=legal;
ods noproctitle;
 
/* using ID= to control each ODS dest */
ods pdf (id=custom) 
  /* this file path is on the SAS workspace */
  file="C:\Projects\report.pdf" 
  columns=2 style=journal;
  title "Types by Cylinders and MSRP stats";
 
proc freq data=sashelp.cars;
  table Type * Cylinders;
run;
 
proc means data=sashelp.cars;
  class Origin;
  var msrp;
run;
 
/* print-friendly image options */
ods pdf (id=custom) dpi=300 startpage=no;
ods graphics / 
  noborder scale=on
  height=2in width=2.5in;
title "Distribution";
 
proc sgplot data=sashelp.cars;
	histogram msrp;
	density msrp;
run;
 
ods pdf (id=custom) close;

Sample result:
Link to PDF
Some notes about this program:

  • The FILE= option on the ODS PDF statement refers to the file that you want to create in the file system of your SAS workspace, not your local machine. SAS Enterprise Guide will offer to download this file for you to view, but if you want complete control over where it lands on your local PC, use the Copy Files task to download it.
  • If you run this program in SAS Enterprise Guide without turning off the other Results formats, the final PDF output won't have all of the attributes you expect. For example, the graph might look slightly different. So if this PDF is your ultimate objective, be sure to suppress the automatic results from SAS Enterprise Guide.
  • Use this same technique to create custom HTML output, or ODS POWERPOINT (new in 9.4), ODS EPUB (new in 9.4) or even ODS HTML5 (hey! new in 9.4).

For a minimalist, fast-running SAS program, you can use this technique to suppress the fancy (bulkier) ODS results and rely on the old-school LISTING format. In the Results properties for your program, uncheck all output destinations except for Text. If your program generates only text-based output that you want to view quickly, this will turn SAS Enterprise Guide into a speedy-quick results-generating machine...just like you might be accustomed to from your old PC SAS days.

See also

Destination Known: Programmatically Controlling Your Output in SAS Enterprise Guide, by Aaron Hill
[VIDEO] New Destinations in ODS: PowerPoint, HTML5, EPUB, EXCEL
Tip Sheet for ODS PDF

tags: ods, SAS 9.4, SAS Enterprise Guide, SAS programming