Problem Solvers

8月 162019
 

The Output Delivery System (ODS) Graphics procedures provide many options to give you control over the look of your output. However, there are times when your output does not look like you thought it would.

This blog discusses how to solve some common output-related problems that we hear about in Technical Support.

All of the examples in this blog relate to creating scatter plots and bar charts from the same data set, SASHELP.CLASS. This data set, included in your SAS® installation, provides information about heights and ages for both male and female students.

Colors in the output are not as desired

Using the STYLEATTRS statement

The STYLEATTRS statement enables you to define attributes, such as color, for graphical elements.

In the following SGPLOT procedure example, the STYLEATTRS statement defines the colors for the marker symbols on a scatter plot as either blue or pink:

proc sgplot data=sashelp.class;
   styleattrs datacolors=(blue pink);
   scatter x=age y=height / group=sex
   markerattrs=(symbol=circlefilled);
run;

However, after you submit the code, the resulting plot does not use the specified colors. Instead, you see blue and red:

When defining colors for graphical elements, the DATACOLORS= option defines the colors for filled areas, and the DATACONTRASTCOLORS= option defines the colors for marker symbols and lines.

Because the scatter plot is creating marker symbols, you have to change the STYLEATTRS statement to use the DATACONTRASTCOLORS= option instead of the DATACOLORS= option. Here is the revised code:

proc sgplot data=sashelp.class;
   styleattrs datacontrastcolors=(blue pink);
   scatter x=age y=height / group=sex 
   markerattrs=(symbol=circlefilled);
run;

Now, when you submit the updated code, the correct colors appear:

You can find more information about the STYLEATTRS statement in the STYLEATTRS section of the SAS® 9.4 ODS Graphics: Procedures Guide, Sixth Edition documentation.

Using an attribute map

An attribute map enables you to associate specific values for your plot GROUP= variable with specific graphical attributes.

The attribute map is defined in a data set that includes the following:

  • an ID variable that contains the name of the attribute map definition
  • a VALUE variable that contains the value of the plot statement GROUP= variable
  • any other variables for the attributes that you want to define

In the following example, the attribute map BARCOLORS is defined to associate the group value F with the color pink and the group value M with the color blue. Note that the FILLCOLOR variable is also used to define the colors for the bars of the bar chart.

data attrmap;
   id='barcolors';
      input value $ fillcolor $;
      datalines;
F pink
M blue
;
run;
proc format;
   value $ genderfmt
      'F'='Female'
      'M'='Male';
run;
proc sgplot data=sashelp.class dattrmap=attrmap;
   vbar age / response=height group=sex groupdisplay=cluster 
   nooutline attrid=barcolors;
   format sex $genderfmt.;
run;

However, the output shows blue and red bars, instead of using the pink and blue values that you specified:

In this case, a format is defined to display the group values as Female and Male. The attribute map associates the group values of F and M with the pink and blue bar colors that you want, so the values do not match.

You need to change the attribute map VALUE variable so that it contains the formatted value of the GROUP= variable. Here is the first part of the code again, highlighted to show where it has changed:

With the updated values, the output now displays the correct colors:

You can find more information about attribute maps in the SG Attribute Maps section of the SAS® 9.4 ODS Graphics: Procedures Guide, Sixth Edition documentation.

Symbols in the output are not as desired

In the following PROC SGPLOT example, the STYLEATTRS statement defines the colors for the marker symbols on a scatter plot as either blue or pink. Also, the marker symbols should be either filled circles or filled squares:

ods html style=styles.htmlblue;
proc sgplot data=sashelp.class;
   styleattrs datacontrastcolors=(blue pink) 
   datasymbols=(circlefilled squarefilled);
   scatter x=age y=height / group=sex;
run;

You submit the code using the STYLES.HTMLBLUE style. The output shows all the symbols as circles, and none are squares:

The ATTRPRIORITY ODS Graphics option determines how attributes are cycled. The default value for the ATTRPRIORITY option is defined in the style that is being used.

The STYLES.HTMLBLUE style sets the default value COLOR for the ATTRPRIORITY option. This COLOR value cycles the symbols through your specified colors before the second symbol is generated.

You want to set the ATTRPRIORITY ODS Graphics option to NONE in an ODS GRAPHICS statement. That ODS GRAPHICS statement then prevents the symbols from cycling through the colors list:

ods graphics /attrpriority=none;
ods html style=styles.htmlblue;
proc sgplot data=sashelp.class;
   styleattrs datacontrastcolors=(blue pink) 
   datasymbols=(circlefilled squarefilled);
   scatter x=age y=height / group=sex;
run;

In the updated output, squares are now seen in the correct color, pink:

You can read more about how attributes are cycled in the following blog post by Rick Wicklin:
Attrs, attrs, everywhere: The interaction between ATTRPRIORITY, CYCLEATTRS, and STYLEATTRS in ODS graphics

Annotation is not placed in the output as desired

Adding an oval

In this example, you want the resulting scatter plot to contain an oval around the circle that represents the tallest student:

proc sql;
   create table maxheight as
   select height, age
   from sashelp.class
        having height=max(height);
quit;
 
data anno;
   set maxheight;
       drawspace='datavalue';
       function='oval';
       x=age;
       y=height;
       width=6;
       height=6;
       linecolor='red';
run;
 
proc sgplot data=sashelp.class sganno=anno;
   scatter x=age y=height;
      xaxis offsetmax=0.1 offsetmin=0.1;
      yaxis offsetmax=0.1 offsetmin=0.1;
run;

After you submit the code, you notice that the resulting plot does not include the oval:

If you have used annotation in SAS/GRAPH® software, you might be accustomed to using the X and Y variables in the annotation data set to indicate the location of the annotation. However, ODS Statistical Graphics (SG) annotation uses X1 and Y1 variables for the location of the annotation.

Therefore, you need to change the X and Y variables in the annotation data set to X1 and Y1 instead:

data anno;
   set maxheight;
       drawspace='datavalue';
       function='oval';
       x1=age;
       y1=height;
       width=6;
       height=6;
       linecolor='red';
run;

In the next version of the scatter plot, the oval now appears:

Adding a text label

In this example, you want to place a text label next to the circle that represents the tallest student.

proc sql;
   create table maxheight as
   select height, age, name
   from sashelp.class
        having height=max(height);
quit;
 
data anno;
   set maxheight;
       drawspace='datavalue';
       function='label';
       x1=age;
       y1=height;
       label=name;
       textsize=10;
       textcolor='red';
       anchor='bottom';
run;
 
proc sgplot data=sashelp.class sganno=anno;
   scatter x=age y=height;
      xaxis offsetmax=0.1 offsetmin=0.1;
      yaxis offsetmax=0.1 offsetmin=0.1;
run;

However, after you run this code, the output does not include the text label:

Again, if you are a SAS/GRAPH user, you might assume that the LABEL function can place text on a plot. However, ODS SG annotation needs to use the TEXT function instead.

In the previous DATA step, you need to change the FUNCTION variable so that it contains the value TEXT:

data anno;
   set maxheight;
       drawspace='datavalue';
       function='text';
       x1=age;
       y1=height;
       label=name;
       textsize=10;
       textcolor='red';
       anchor='bottom';
run;

After you revise the DATA step and resubmit your code, you then see that the text label appears where intended:

You can find more information about SG annotation in the SG Annotation section of the SAS® 9.4 ODS Graphics: Procedures Guide, Sixth Edition documentation.

Summary

These are just a few examples to demonstrate some of the common output-related problems that we hear about in Technical Support. If your graphical output does not appear as you wanted, consider the options that you are using and make sure that you are using the correct option.

Learn More

How to fix common problems in output from ODS Graphics procedures was published on SAS Users.

7月 192019
 
The RANK procedure (PROC RANK) is useful for ranking numeric variables in a data set across observations. You often see PROC RANK used to rank data into quartiles, deciles, or percentiles. This action requires that you use the GROUPS= option in the PROC RANK statement.

This blog answers three questions related to using PROC RANK with groups and ties. Note that question two actually provide an alternative for using the DATA step when PROC RANK cannot provide what you need.

  1. What does PROC RANK do behind the code when you use the GROUPS= option in the PROC RANK statement?
  2. What do you do if you want equal groups, regardless of tied values?
  3. What do you do if you want groups to start at 1 rather than 0?

What does PROC RANK do when you use the GROUPS= option?

Most of us just accept the ranked values that are calculated by PROC RANK. But have you ever tried to figure out the calculation that happens behind the code? When you use the GROUPS= option in the PROC RANK statement, the values are assigned to groups that range from 0 to the number-of-groups minus 1, based on tied values. If you have many tied values, you might not obtain the number of groups that you request because observations with the same value are assigned to the same group.

The formula for calculating group values is as follows:

FLOOR(rank*k/(n+1))

In this formula:

  • rank is the data value's rank order
  • k is the value of the GROUPS= option
  • n is the number of nonmissing values

Consider the following example. If you want to see the original value as well as the ranking, use both the VAR and RANKS statements in your PROC RANK step. The RANKS variable contains the rank value. If you use a BY statement, the raw rankings start over in each BY group.

Example 1

data;
input x;
cards;
1
1
1
1
1
1
8
8
;
run;
proc rank data=test groups=5 out=test_rank 
 ties=mean /* low and high */;
var x;
ranks rank_x;
run;
 
proc print data=test_rank;
run;

Output

x rank_x
1 1
1 1
1 1
1 1
1 1
1 1
8 4
8 4

 
The following table shows the variable values, the raw ranks, the ties values, and the resulting ranks:

X RAW_RANK TIES=MEAN TIES=LOW TIES=HIGH RANK_MEAN RANK_LOW RANK_HIGH
1 1 3.5 1 6 1 0 3
1 2 3.5 1 6 1 0 3
1 3 3.5 1 6 1 0 3
1 4 3.5 1 6 1 0 3
1 5 3.5 1 6 1 0 3
1 6 3.5 1 6 1 0 3
8 7 7.5 7 8 4 3 4
8 8 7.5 7 8 4 3 4

 
Using the formula that is shown previously, k=5 and n=8. Since TIES=MEAN, you sum the raw ranks of the tied values of X and divide by the number of observations. For X=1, the rank is (1+2+3+4+5+6)=21/6=3.5. For X=2, the rank is (7+8)=15/2=7.5. Similarly, if you use TIES=LOW, for X=1, the rank is 1; for X=2, the rank is 7. Finally, if you use TIES=HIGH, for X=1, the rank is 6; for X=2, the rank is 8. When you insert those values into the formula for the first observation, you obtain the following results:

TIES=MEAN: Floor(3.5*5/9)=Floor(1.9)=1
TIES=LOW: Floor(1*5/9)=Floor(0.5)=0
TIES=HIGH: Floor(6*5/9)=Floor(3.3)=3

What do you do if you want equal groups, regardless of tied values?

Suppose that you want to create groups that have the same number of observations in each one, regardless of tied values. PROC RANK cannot do this. However, you can use the DATA step to accomplish this task.

You need to sort the data set by the ranking variable and then use the same formula in the DATA step, as shown below.

Example 2

proc sort data=test;
by x;
run;
 
data ranks;
set test nobs=numobs;
group=floor(_n_*5/(numobs+1));
run;
 
proc print data=ranks;
run;

Output

x group
1 0
1 1
1 1
1 2
1 2
1 3
8 3
8 4

 

What do you do if you want the groups to start at 1 rather than 0?

When you use the GROUPS= option, the values that are assigned to the groups start at 0. There is no way to indicate for PROC RANK to start the groups at 1. However, once you have the data set with the ranked values, you can add 1 using DATA step logic, as shown in this example:

Example 3

data test;
input y; 
cards;
11
22
10
15
25
;
run;
 
proc sort data=test; 
by y;
run;
 
proc rank data=test out=test_rank1 groups=3;
var y;
ranks rank_y;
run;
 
data test_rank1; 
set test_rank1; rank_y+1;
run;
 
proc print data=test_rank1;
run;

Output

y rank_y
10 1
11 2
15 2
22 3
25 3

 

Conclusion

PROC RANK has many statistical applications, such as helping you understand how your data is distributed. Hopefully, this blog has provided you with a better understanding of how ranks are determined when tied values are present in the data and when you want to assign those ranks to groups.

How the RANK procedure calculates ranks with groups and ties was published on SAS Users.

6月 212019
 

For every project in SAS®, the first step is almost always making your data available. This blog shows you how to load three of the most common input data types—a data set, a text file, and a Microsoft Excel file—into SAS® Cloud Analytic Services (CAS) tables.

The three methods that I show here are the three easiest ways to load each data type into CAS. Multiple tools can load data into CAS, but I am showing the tools that I consider the easiest to use and that are probably the most familiar to SAS programmers.

You need to place your data in a location that can be accessed by the programming environment that is used to access CAS. The most common programming environment that accesses CAS is SAS® Studio. Input data files that are used in CAS are going to be very large. You will need to use an SFTP tool to move your data from your PC to a directory that can be accessed by your SAS Studio session. (Check with your system administrator to see what the preferred tool is at your site.)

After your data is in a location that can be accessed by the programming environment, you need to start a CAS session. This is done with the CAS statement; here is the syntax:

cas session-name <option(s)>;

The options that you specify depend on how your system administrator configured your environment. For example, I asked my system administrator to set it up so that the only thing I need to do is issue the following statement:

cas;

That statement then creates a CAS session with the default name of CASAUTO, with an active caslib of CASUSER.

After you establish your CAS session, you can start loading data.

Load a SAS data set

The easiest way to load SAS data into CAS is to use a DATA step. The basic syntax is the same as it is when you are creating a SAS data set. The key difference is that the libref that is listed in the DATA step must point to a caslib.

The following example accesses SASHELP.CARS and then creates the table CARS in the CASUSER caslib.

cas;                   /* log on to the CAS session */
   caslib _all_ assign;   /* create a libref for each active 
                             caslib */
 
   data casuser.cars;     /* create the table in the caslib */
      set sashelp.cars;
   run;

The one thing to note about this code is that this DATA step is running in SAS and not CAS. A DATA step runs in CAS only if the input and output librefs are both using the CAS engine and only if it uses language elements that CAS supports. In this example, the SASHELP libref was not created with the CAS engine, so the step must run in SAS.

There are no calculations in this step, so there is no effect on performance.

When you load a data set into a CAS table, one task that you might want to perform is to promote the table. The following DATA step shows how to promote a table as you create it:

data casuser.cars(promote=yes);
      set sashelp.cars;
   run;

Promoting a table gives it a global scope. You can then access the table in multiple sessions, and the table is also available in SAS® Visual Analytics. The PROMOTE= data set option can be used with all three of the examples in this blog.

Load a delimited text file

The easiest way to load a delimited text file and store it as a CAS table is to use another familiar SAS step, the IMPORT procedure. The syntax is going to be basically the same as it is in SAS. Again, the key difference is that you need to point to a caslib in the OUT= option of the PROC IMPORT statement.

If you are running SAS® Studio 5.1, a common location for a text file that needs to be loaded into CAS is the SAS Content folder. This folder is a predefined repository for text files that you need to access from SAS Studio.

In order to access the files from SAS Content, you need to use the FILESRVC access method with the FILENAME statement. This method enables you to store and retrieve content using the SAS® Viya® Files service. Here is the basic syntax:

filename fileref filesrvc folderpath='path'
            filename='name';

For more information about this access method, see SAS 9.4 Global Statements Reference.

In the following example, PROC IMPORT and the FILESRVC access method are used to load the class.csv file from the SAS Content folder. The resulting ALLCLASS table is written to the CASUSER caslib.

filename myfile filesrvc folderpath='/Users/saskir'
            filename='class.csv';
   proc import datafile=myfile out=casuser.allclass(promote=yes)
        dbms=csv;
   run;

Load an Excel file

This section shows you how to load an Excel file into a CAS table by using PROC IMPORT. Loading an Excel file using PROC IMPORT requires that you have SAS/ACCESS® Interface to PC Files. If you are unsure whether you have this product, you can write all of your licensed products to the log by using the SETINIT procedure:

proc setinit;
   run;

You should see the following in the log when the product is licensed:

---SAS/ACCESS Interface to PC Files

After you confirm that you have this product, you can adapt the following PROC IMPORT code. This example loads the ReportTest.xlsx file and stores it in a table named ReportTest in the CASUSER caslib.

cas;
   caslib _all_ assign;
 
   proc import datafile='/viyashare/ReportTest.xlsx'
        out=casuser.ReportTest dbms=xlsx;
   run;

There are other methods

The purpose of this blog was to show you the easiest ways to load a SAS data set, text file, and Excel file into CAS. Although there are multiple ways to accomplish these tasks, both programmatically and interactively, these methods are the easiest and most straightforward ways to get data into CAS.

Learn the three easiest ways to load data into CAS tables was published on SAS Users.

4月 242019
 
The ODS Excel destination, which became a production feature in SAS 9.4M3 (TS1M3), generates Microsoft Excel workbooks in native XLSX format. This destination generates multiple worksheets per workbook with each output object (e.g., a table or graphic) the destination encounters by default. The ODS Excel destination is also flexible, enabling you to vertically control the worksheet and place output objects wherever you want. This blog demonstrates the destination’s flexibility and how you can modify its default behavior by using the ODS EXCEL statement's SHEET_INTERVAL= option.

Adding tables and graphics on the same Microsoft Excel worksheet

By default, the ODS Excel destination adds a new worksheet for each table and graphic. However, at times, you might not want to use this default behavior. If you want more control over this, the SHEET_INTERVAL= ODS Excel option determines when a new worksheet is created. Valid values for the SHEET_INTERVAL= option include:

  • TABLE (the default value) - new sheet for each table in output
  • NONE - keep the output that follows on the current sheet
  • PAGE - new sheet for each page of SAS output
  • PROC - new sheet beginning at the PROC step boundary
  • BYGROUP - new sheet for each BY group of output
  • NOW - begin a new sheet immediately

The value NOW, new for SAS 9.4M5 (TS1M5), triggers the creation of a new worksheet after the destination encounters the next output object.
As an example of opting not to use the default behavior, consider a case where you have a CONTENTS procedure without any options. This procedure generates three separate worksheets with the data-set attributes, the engine host information, and the variable list.

The following table shows the default output that you receive with three individual worksheets:

However, if you want to place all three objects on a single worksheet, you can do that by setting the option SHEET_INTERVAL="NONE". The option setting SHEET_INTERVAL="PROC" could also be used in this example which would create a new worksheet only when a new procedure is encountered.
The following example illustrates how to use this option to include all your output on the same worksheet:

ods excel file="c:\test.xlsx" options sheet_interval="none");
proc contents data=sashelp.class;
run; 
ods excel close;

Output

Adding text and tables to a new worksheet

Two of the most popular ways to add text on worksheets are to use either the ODS TEXT= statement or the ODSTEXT procedure with the Excel destination. The following example adds text to a worksheet by using the ODS TEXT= statement. You include this statement before each PRINT procedure in this example:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males");
ods text="Data for Male Patients";
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Notice that the first text string appears at the top of the first worksheet as expected. However, the text from the second ODS TEXT= statement appears at the bottom of this same page rather than at the top of the next worksheet containing the related data table. This behavior illustrates that the ODS TEXT= option is not very flexible. There is no good way to solve this issue.

However, you can use the SAS 9.4 ODSTEXT procedure in combination with the SHEET_INTERVAL= option to move the text string to the appropriate worksheet.
The following example uses PROC ODSTEXT and the SHEET_INTERVAL= option to move the text string "Statistics for Male Patients" to the top of the second worksheet:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males" sheet_interval="now");
ods excel options(sheet_name="Males" sheet_interval="none");
 
proc odstext;
   p "Data for Male Patients";
run;
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Adding multiple tables or graphs on the same worksheet

This final example demonstrates how you can use the SHEET_INTERVAL= option to add multiple tables and graphics to the same Excel worksheet. First, we use the SHEET_INTERVAL="NONE" option in the first ODS EXCEL statement to place the first table and graph on the same worksheet. Then, the SHEET_INTERVAL="NOW" option is included in the second ODS EXCEL statement option to create a second worksheet and write the second table and graph to that worksheet:

ods graphics / height=2.5in width=3.5in;
ods excel file="c:\scratch\test.xlsx" options(sheet_interval="none");
 
proc print data=sashelp.class(where=(sex="F"));
run;
 
proc sgplot data=sashelp.class(where=(sex="F"));
scatter x=age y=height;
run;
 
ods excel options(sheet_interval="now");
 
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
proc sgplot data=sashelp.class(where=(sex="M"));
scatter x=age y=height;
run;
 
ods excel close;

Output

Learn more

Control the location of tables, graphs, and text with ODS Excel was published on SAS Users.

4月 242019
 
The ODS Excel destination, which became a production feature in SAS 9.4M3 (TS1M3), generates Microsoft Excel workbooks in native XLSX format. This destination generates multiple worksheets per workbook with each output object (e.g., a table or graphic) the destination encounters by default. The ODS Excel destination is also flexible, enabling you to vertically control the worksheet and place output objects wherever you want. This blog demonstrates the destination’s flexibility and how you can modify its default behavior by using the ODS EXCEL statement's SHEET_INTERVAL= option.

Adding tables and graphics on the same Microsoft Excel worksheet

By default, the ODS Excel destination adds a new worksheet for each table and graphic. However, at times, you might not want to use this default behavior. If you want more control over this, the SHEET_INTERVAL= ODS Excel option determines when a new worksheet is created. Valid values for the SHEET_INTERVAL= option include:

  • TABLE (the default value) - new sheet for each table in output
  • NONE - keep the output that follows on the current sheet
  • PAGE - new sheet for each page of SAS output
  • PROC - new sheet beginning at the PROC step boundary
  • BYGROUP - new sheet for each BY group of output
  • NOW - begin a new sheet immediately

The value NOW, new for SAS 9.4M5 (TS1M5), triggers the creation of a new worksheet after the destination encounters the next output object.
As an example of opting not to use the default behavior, consider a case where you have a CONTENTS procedure without any options. This procedure generates three separate worksheets with the data-set attributes, the engine host information, and the variable list.

The following table shows the default output that you receive with three individual worksheets:

However, if you want to place all three objects on a single worksheet, you can do that by setting the option SHEET_INTERVAL="NONE". The option setting SHEET_INTERVAL="PROC" could also be used in this example which would create a new worksheet only when a new procedure is encountered.
The following example illustrates how to use this option to include all your output on the same worksheet:

ods excel file="c:\test.xlsx" options sheet_interval="none");
proc contents data=sashelp.class;
run; 
ods excel close;

Output

Adding text and tables to a new worksheet

Two of the most popular ways to add text on worksheets are to use either the ODS TEXT= statement or the ODSTEXT procedure with the Excel destination. The following example adds text to a worksheet by using the ODS TEXT= statement. You include this statement before each PRINT procedure in this example:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males");
ods text="Data for Male Patients";
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Notice that the first text string appears at the top of the first worksheet as expected. However, the text from the second ODS TEXT= statement appears at the bottom of this same page rather than at the top of the next worksheet containing the related data table. This behavior illustrates that the ODS TEXT= option is not very flexible. There is no good way to solve this issue.

However, you can use the SAS 9.4 ODSTEXT procedure in combination with the SHEET_INTERVAL= option to move the text string to the appropriate worksheet.
The following example uses PROC ODSTEXT and the SHEET_INTERVAL= option to move the text string "Statistics for Male Patients" to the top of the second worksheet:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males" sheet_interval="now");
ods excel options(sheet_name="Males" sheet_interval="none");
 
proc odstext;
   p "Data for Male Patients";
run;
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Adding multiple tables or graphs on the same worksheet

This final example demonstrates how you can use the SHEET_INTERVAL= option to add multiple tables and graphics to the same Excel worksheet. First, we use the SHEET_INTERVAL="NONE" option in the first ODS EXCEL statement to place the first table and graph on the same worksheet. Then, the SHEET_INTERVAL="NOW" option is included in the second ODS EXCEL statement option to create a second worksheet and write the second table and graph to that worksheet:

ods graphics / height=2.5in width=3.5in;
ods excel file="c:\scratch\test.xlsx" options(sheet_interval="none");
 
proc print data=sashelp.class(where=(sex="F"));
run;
 
proc sgplot data=sashelp.class(where=(sex="F"));
scatter x=age y=height;
run;
 
ods excel options(sheet_interval="now");
 
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
proc sgplot data=sashelp.class(where=(sex="M"));
scatter x=age y=height;
run;
 
ods excel close;

Output

Learn more

Control the location of tables, graphs, and text with ODS Excel was published on SAS Users.

4月 082019
 


As word spreads that SAS integrates with open source technologies, people are beginning to explore how to connect, interact with, and use SAS in new ways. More and more users are examining the possibilities and with this comes questions like: How do I code A, integrate B, and accomplish C?

Documentation is plentiful but is undergoing a makeover. People aren’t sure where to go for help – and that's why we're launching the SAS Developers Community, where you can gather to ask questions and get answers.

The community will mirror the activities in existing SAS Communities: Q&A, library articles, tips, technical discussions, etc. We migrated some content from other boards. For example, we moved the content from the Coding on SAS Viya board to the new community. Additionally, we scoured other boards for content that may be better aligned with developers and moved it. We also created some original content. Any good community needs participation by all, so read on and get the 411 on the new Developers Community.

Who is the target audience?

Developers – data scientists, application developers, analysts, programmers and administrators – who need to access SAS resources and/or run SAS procedures. This audience may or may not have SAS programming skills but need to access and analyze data using SAS.

What can developers expect to find?

The Developers Community provides a forum for collaboration, Q&A, and knowledge and resource sharing. The focus will be on developers using open source languages and technology. The community will create synergy between communities.sas.com, developer.sas.com, and github.com/sassoftware. SAS employees and external users will post how-to articles and other items of interest in the library section of the community. This community will not replace the SAS Programming Communities, rather, it will fill a void for non-SAS programmers who have a need/desire to interact with SAS.

When will the community launch?

The Developers Community is live! The site is public, and we've moved existing artifacts to the community. I am attending SAS Global Forum and will be available to answer questions about the new community from our booth in the Quad. Come by and see me!

Where will the community live?

The Developers Community exists on communities.sas.com, under the Developers Category.

Why do we need a community for developers?

Developers need a centralized place to share ideas, ask and answer questions, and discover resources. Currently developers lack a forum to work through things such as authentication, coding, API use, and integration issues. The community will encourage communication, engagement and leadership. Also, the Developers Community will be tightly integrated with the SAS Developers web site and SAS GitHub resources.

How do we go about creating the community?

After seeding the SAS Developer Community with existing discussions, we'll build out a group of SAS developer experts to help monitor the community. The true magic will happen as questions are asked, discussions transpire, and ideas are shared. But we need to your help too. Here is your call to action.

Share the community with your networks, buddies and even family members who may get something out of chatting it up about how to develop in SAS. The livelihood of the community hinges on user interaction. Our current and future users will thank you for it. And you may make a friend while you're at it.

Launching the Developers Community in SAS Communities was published on SAS Users.

4月 012019
 

dividing by zero with SAS

Whether you are a strong believer in the power of dividing by zero, agnostic, undecided, a supporter, denier or anything in between and beyond, this blog post will bring all to a common denominator.

History of injustice

For how many years have you been told that you cannot divide by zero, that dividing by zero is not possible, not allowed, prohibited? Let me guess: it’s your age minus 7 (± 2).

But have you ever been bothered by that unfair restriction? Think about it: all other numbers get to be divisors. All of them, including positive, negative, rational, even irrational and imaginary. Why such an injustice and inequality before the Law of Math?

We have our favorites like π, and prime members (I mean numbers), but zero is the bottom of the barrel, the lowest of the low, a pariah, an outcast, an untouchable when it comes to dividing by. It does not even have a sign in front of it. Well, it’s legal to have, but it’s meaningless.

And that’s not all. Besides not being allowed in a denominator, zeros are literally discriminated against beyond belief. How else could you characterize the fact that zeros are declared as pathological liars as their innocent value is equated to FALSE in logical expressions, while all other more privileged numbers represent TRUE, even the negative and irrational ones!

Extraordinary qualities of zeros

Despite their literal zero value, their informational value and qualities are not less than, and in many cases significantly surpass those of their siblings. In a sense, zero is a proverbial center of the universe, as all the other numbers dislocated around it as planets around the sun. It is not coincidental that zeros are denoted as circles, which makes them forerunners and likely ancestors of the glorified π.

Speaking of π, what is all the buzz around it? It’s irrational. It’s inferior to 0: it takes 2 π’s to just draw a single zero (remember O=2πR?). Besides, zeros are not just well rounded, they are perfectly rounded.

Privacy protection experts and GDPR enthusiasts love zeros. While other small numbers are required to be suppressed in published demographical reports, zeros may be shown prominently and proudly as they disclose no one’s personally identifiable information (PII).

No number rivals zero. Zeros are perfect numerators and equalizers. If you divide zero by any non-zero member of the digital community, the result will always be zero. Always, regardless of the status of that member. And yes, zeros are perfect common denominators, despite being prohibited from that role for centuries.

Zeros are the most digitally neutral and infinitely tolerant creatures. What other number has tolerated for so long such abuse and discrimination!

Enough is enough!

Dividing by zero opens new horizons

Can you imagine what new opportunities will arise if we break that centuries-old tradition and allow dividing by zero? What new horizons will open! What new breakthroughs and discoveries can be made!

With no more prejudice and prohibition of the division by zero, we can prove virtually anything we wish. For example, here is a short 5-step mathematical proof of “4=5”:

1)   4 – 4 = 10 – 10
2)   22 – 22 = 5·(2 – 2)
3)   (2 + 2)·(2 – 2) = 5·(2 – 2) /* here we divide both parts by (2 – 2), that is by 0 */
4)   (2 + 2) = 5
5)   4 = 5

Let’s make the next logical step. If dividing by zero can make any wish a reality, then producing a number of our choosing by dividing a given number by zero scientifically proves that division by zero is not only legitimate, but also feasible and practical.

As you will see below, division by zero is not that easy, but with the power of SAS, the power to know and the powers of curiosity, imagination and perseverance nothing is impossible.

Division by zero - SAS implementation

Consider the following use case. Say you think of a “secret” number, write it on a piece of paper and put in a “secret” box. Now, you take any number and divide it by zero. If the produced result – the quotient – is equal to your secret number, wouldn’t it effectively demonstrate the practicality and magic power of dividing by zero?

Here is how you can do it in SAS. A relatively “simple”, yet powerful SAS macro %DIV_BY_0 takes a single number as a numerator parameter, divides it by zero and returns the result equal to the one that is “hidden” in your “secret” box. It is the ultimate, pure artificial intelligence, beyond your wildest imagination.

All you need to do is to run this code:

 
data MY_SECRET_BOX;        /* you can use any dataset name here */
   MY_SECRET_NUMBER = 777; /* you can use any variable name here and assign any number to it */
run;
 
%macro DIV_BY_0(numerator);
 
   %if %sysevalf(&numerator=0) %then %do; %put 0:0=1; %return; %end;
   %else %let putn=&sysmacroname; 
   %let %sysfunc(putn(%substr(&putn,%length(&putn)),words.))=
   %sysevalf((&numerator/%sysfunc(constant(pi)))**&sysrc);  
   %let a=com; %let null=; %let nu11=%length(null); 
   %let com=*= This is going to be an awesome blast! ;
   %let %substr(&a,&zero,&zero)=*Close your eyes and open your mind, then;
   %let imagine = "large number like 71698486658278467069846772 Bytes divided by 0";
   %let O=%scan(%quote(&c),&zero+&nu11); 
   %let l=%scan(%quote(&c),&zero);
   %let _=%substr(%scan(&imagine,&zero+&nu11),&zero,&nu11);
   %let %substr(&a,&zero,&zero)%scan(&&&a,&nu11+&nu11-&zero)=%scan(&&&a,-&zero,!b)_;
   %do i=&zero %to %length(%scan(&imagine,&nu11)) %by &zero+&zero;
   %let null=&null%sysfunc(&_(%substr(%scan(&imagine,&nu11),&i,&zero+&zero))); %end;
   %if &zero %then %let _0=%scan(&null,&zero+&zero); %else;
   %if &nu11 %then %let _O=%scan(&null,&zero);
   %if %qsysfunc(&O(_&can)) %then %if %sysfunc(&_0(&zero)) %then %put; %else %put;
   %put &numerator:0=%sysfunc(&_O(&zero,&zero));
   %if %sysfunc(&l(&zero)) %then;
 
%mend DIV_BY_0;
 
%DIV_BY_0(55); /* parameter may be of any numeric value */

When you run this code, it will produce in the SAS LOG your secret number:

55:0=777

How is that possible without the magic of dividing by zero? Note that the %DIV_BY_0 macro has no knowledge of your dataset name, nor the variable name holding your secret number value to say nothing about your secret number itself.

That essentially proves that dividing by zero can practically solve any imaginary problem and make any wish or dream come true. Don’t you agree?

There is one limitation though. We had to make this sacrifice for the sake of numeric social justice. If you invoke the macro with the parameter of 0 value, it will return 0:0=1 – not your secret number - to make it consistent with the rest of non-zero numbers (no more exceptions!): “any number, except zero, divided by itself is 1”.

Challenge

Can you crack this code and explain how it does it? I encourage you to check it out and make sure it works as intended. Please share your thoughts and emotions in the Comments section below.

Disclosure

This SAS code contains no cookies, no artificial sweeteners, no saturated fats, no psychotropic drugs, no illicit substances or other ingredients detrimental to your health and integrity, and no political or religious statements. It does not collect, distribute or sell your personal data, in full compliance with FERPA, HIPPA, GDPR and other privacy laws and regulations. It is provided “as is” without warranty and is free to use on any legal SAS installation. The whole purpose of this blog post and the accompanied SAS programming implementation is to entertain you while highlighting the power of SAS and human intelligence, and to fool around in the spirit of the date of this publication.

Dividing by zero with SAS was published on SAS Users.

3月 152019
 
SAS makes it easy for you to create a large amount of procedure output with very few statements. However, when you create a large amount of procedure output with the Output Delivery System (ODS), your SAS session might stop responding or run slowly. In some cases, SAS generates a “Not Responding” message. Beginning with SAS® 9.3, the SAS windowing environment creates HTML output by default and enables ODS Graphics by default. If your code creates a large amount of either HTML output or ODS Graphics output, you can experience performance issues in SAS. This blog article discusses how to work around this issue.

Option 1: Enable the Output window instead of the Results Viewer window

By default, the SAS windowing environment with SAS 9.3 and SAS® 9.4 creates procedure output in HTML format and displays that HTML output in the Results Viewer window. However, when a large amount of HTML output is displayed in the Results Viewer window, performance might suffer. To display HTML output in the Results Viewer window, SAS uses an embedded version of Internet Explorer within the SAS environment. And because Internet Explorer does not process large amounts of HTML output well, it can slow down your results.

If you do not need to create HTML output, you can display procedure output in the Output window instead. To do so, add the following statements to the top of your code before the procedure step:

   ods _all_ close; 
   ods listing;

The Output window can show results faster than HTML output that is displayed in the Results Viewer window.

If you want to enable the Output window via the SAS windowing environment, take these steps:

    1. Choose Tools ► Options ► Preferences.
    2. Click the Results tab.
    3. In this window, select Create listing and clear the Create HTML check box.
    4. Click OK.

A large amount of output in the Output window, which typically does not cause a performance issue, might still generate an “Output window is full” message. In that case, you can route your LISTING output to a disk file. Use either the PRINTTO procedure or the ODS LISTING statement with the FILE= option. Here is an example:

   ods _all_ close; 
   ods listing file="sasoutput.lst"; 

Option 2: Disable ODS Graphics

Beginning with SAS 9.3, the SAS windowing environment enables ODS Graphics by default. Therefore, most SAS/STAT® procedures now create graphics output automatically. Naturally, graphics output can take longer to create than regular text output. If you are running a SAS/STAT procedure but you do not need to create graphics output, add the following statement to the code before the procedure step:

   ods graphics off; 

If you want to set this option via the SAS windowing environment, take these steps:

    1. Choose Tools ► Options ► Preferences.
    2. Click the Results tab.
    3. In this window, clear the Use ODS Graphics check box.
    4. Click OK.

For maximum efficiency, you can combine the ODS GRAPHICS OFF statement with the statements listed in the previous section, as shown here:

   ods _all_ close;
   ods listing;
   ods graphics off; 

Option 3: Write ODS output to disk

You can ask SAS to write ODS output to disk but not to create output in the Results Viewer window. To do so, add the following statement to your code before your procedure step:

   ods results off;

Later in your SAS session, if you decide that you want to see output in the Results Viewer window, submit this statement:

   ods results on;

If you want to disable the Results Viewer window via the SAS windowing environment, take these steps:

    1. Choose Tools ► Options ► Preferences.
    2. Click the Results tab.
    3. In this window, clear the View results as they are generated check box.
    4. Click OK.

The ODS RESULTS OFF statement is a valuable debugging tool because it enables you to write ODS output to disk without viewing it in the Results Viewer window. You can then inspect the ODS output file on disk to check the size of it (before you open it).

Option 4: Suppress specific procedure output from the ODS results

In certain situations, you might use multiple procedure steps to send output to ODS. However, if you want to exclude certain procedure output from being written to ODS, use the following statement:

   ods exclude all;

Ensure that you place the statement right before the procedure step that contains the output that you want to suppress.

If necessary, use the following statement when you want to resume sending subsequent procedure output to ODS:

   ods exclude none;

Five reasons to use ODS EXCLUDE to suppress SAS output discusses the ODS EXCLUDE statement in more detail.

Conclusion

Certain web browsers display large HTML files better than others. When you use SAS to create large HTML files, you might try using a web browser such as Chrome, Firefox, or Edge instead of Internet Explorer. However, even browsers such as Chrome, Firefox, and Edge might run slowly when processing a very large HTML file.

Instead, as a substitute for HTML, you might consider creating PDF output (with the ODS PDF destination) or RTF output (with the ODS RTF destination). However, if you end up creating a very large PDF or RTF file, then Adobe (for PDF output) and Microsoft Word (for RTF output) might also experience performance issues.

The information in this blog mainly pertains to the SAS windowing environment. For information about how to resolve ODS issues in SAS® Enterprise Guide®, refer to Take control of ODS results in SAS Enterprise Guide.

How to view or create ODS output without causing SAS® to stop responding or run slowly was published on SAS Users.

2月 152019
 
Beginning with SAS® 9.4, you can embed graphics output within HTML output using the ODS HTML5 destination. This technique works with SAS/GRAPH® procedures (such as GPLOT and GCHART), SG procedures (such as SGPLOT and SGRENDER), and when you create graphics output with ODS Graphics enabled. Most (if not all) existing web browsers support graphics output embedded in HTML5 output.

Note: The default graphics output format for the ODS HTML5 destination is Scalable Vector Graphics (SVG). SVG documents display clearly at any size in any viewer or browser that supports SVG. So, SVG files are ideal for display on a computer monitor, PDA, or cell phone; or printed documents. Because it's a vector graphic, a single SVG document can be transformed to any screen resolution without compromising the clarity of the document. Here's an example:

The same SVG graph, scaled at 90% and then at 200%. But 100% crisp!

SAS/GRAPH procedures

When you use the ODS HTML5 destination with a SAS/GRAPH procedure, specify a value of SVG, PNG, or JPEG for the DEVICE option in the GOPTIONS statement. The following sample PROC GPLOT code embeds SVG graphics inside the resulting HTML output:

goptions device=svg;
ods _all_ close;  
ods html5 path="c:\temp" file="svg_graph.html"; 
symbol1 i=none v=squarefilled; 
proc gplot data=sashelp.cars; 
  plot mpg_city * horsepower;   
  where make="Honda"; 
run;
quit;  
ods html5 close; 
ods preferences;

Note that the ODS PREFERENCES statement above resets the ODS environment back to its default settings when you use the SAS windowing environment.

When you use the PNG or JPEG device driver with the ODS HTML5 destination, add the BITMAP_MODE="INLINE" option to the ODS HTML5 statement. Here is an example:

goptions device=png;
ods _all_ close; 
ods html5 path="c:\temp" file="png_graph.html"     options(bitmap_mode="inline");
symbol1 i=none v=squarefilled; 
proc gplot data=sashelp.cars; 
  plot mpg_city * horsepower;   
  where make="Honda"; 
run;
quit;  
ods html5 close; 
ods preferences;

ODS Graphics and SG procedures

When you use SG procedures and ODS Graphics, specify a value of SVG, PNG, or JPEG for the OUTPUTFMT option in the ODS GRAPHICS statement. The following sample code uses PROC SGPLOT to embed SVG graphics inside the HTML output with the ODS HTML5 destination:

ods _all_ close; 
ods html5 path="c:\temp" file="svg_graph.html"; 
ods graphics on / reset=all outputfmt=svg;
proc sgplot data=sashelp.cars; 
  scatter y=mpg_city x=horsepower / markerattrs=(size=9PT symbol=squarefilled);   
  where make="Honda"; 
run;
ods html5 close; 
ods preferences;  

The following sample code uses PROC SGPLOT to embed PNG graphics inside the HTML output with the ODS HTML5 destination:

ods _all_ close; 
ods html5 path="c:\temp" file="png_graph.html" options(bitmap_mode="inline");   
      ods graphics on / reset=all outputfmt=png;
proc sgplot data=sashelp.cars; 
  scatter y=mpg_city x=horsepower / markerattrs=(size=9PT symbol=squarefilled);   
  where make="Honda"; 
run;
      ods html5 close; 
      ods preferences; 

The technique above also works when you use the ODS GRAPHICS ON statement with other procedures that produce graphics output (such as the LIFETEST procedure).

Note that the ODS HTML5 destination supports the SAS Graphics Accelerator. The SAS Graphics Accelerator enables users with visual impairments or blindness to create, explore, and share data visualizations. It supports alternative presentations of data visualizations that include enhanced visual rendering, text descriptions, tabular data, and interactive sonification. Sonification uses non-speech audio to convey important information about the graph.

You can use the ODS HTML5 destination in most situations where you need to embed all of your output into a single HTML output location. For example, when you email HTML output as an attachment or when you create graphics output via a SAS stored process. If you currently use the ODS HTML destination, you might want to experiment with the ODS HTML5 destination to see whether it meets your needs even if you cannot completely switch to it yet.

Embed scalable graphics using the ODS HTML5 destination was published on SAS Users.

1月 182019
 
Interested in learning about what's new in a software release? What if you want to know whether anything has changed in a SAS product? Or whether there are steps that you need to take before you upgrade to a new software release?
 
The online SAS product documentation for SAS® 9.4 and SAS® Viya® contains new sections that provide these answers. The following sections are usually listed on the left-hand side of the table of contents for the online Help document:
 
“What’s New in Base SAS: Details”
“What’s New in SAS 9.4 and SAS Viya”
“SAS Guide to Software Updates and Product Changes”
Note: To make the product-change information easier to find, this section was retitled for SAS® 9.4M6 and SAS® Viya® 3.4. For documentation about previous releases of SAS 9.4, this section is called “SAS Guide to Software Updates.” The information about product changes is included in a subsection called “Product Details and Requirements.” Although the title is different in newer documentation, the content remains the same.

What's available in each section?

• “What’s New” contains information about new features. For example, in SAS 9.4M6, “What’s New” discusses a new ODS destination for Word and a new procedure called PROC SGPIE.
 
• The “SAS Guide to Software Updates and Product Changes” includes the following subsections:
      o A section on software updates, which is for customers who are upgrading to a new release. (FYI: A software update is any modification that SAS provides for existing software. An upgrade is a new release of SAS. A maintenance release is a collection of updates that are applied to the currently installed software.)
      o Another subsection discusses product details and requirements. In it, you will find information about values or settings that have changed from one release to the next. For example, for SAS 9.4M6, the default style for HTML5 output changed from HTMLBlue to HTMLEncore. Another example is for SAS® 9.4M0, when the YEARCUTOFF= system option changed from 1920 to 1926.

Other links to these resources

In the “What’s New” documentation, there is a link in each section to the corresponding product topic in “SAS Guide to Software Updates and Product Changes.”

For example, when you scroll through this SAS Studio page, you see references both to various “What’s New” pages for different versions of SAS Studio and to “SAS Guide to Software Updates and Product Changes.”

In “What's New in Base SAS: Details,” you can search by the software and maintenance release to find new features. Beginning with SAS 9.4, new features for maintenance releases are introduced using the SAS 9.4Mx notation. For example, in the search box on the page, you can enter 9.4M6.

Final thoughts

With these new online Help sections, you can find information quickly about new features of the current SAS release, as well as what has changed from the previous release. As always, we welcome your feedback and suggestions for improving the documentation.

Special thanks

Elizabeth Downes and Marie Dexter in SAS Documentation Development were very willing to make the requested wording changes in the documentation. They also contributed to the content of this article. Thanks to both for their time and effort!

Navigating SAS documentation to find out about new, modified, and updated features was published on SAS Users.