Note: This sample references SampleCube, which is available in Sample 26041.

When computing percentages within OLAP, there are several different options to consider:

   CREATE MEMBER [SampleCube].[Measures].[Percent of Parent] as
   '[Measures].[ActualSum] / ([Measures].[ActualSum], [Time].currentmember.parent), 
   format_string="Percent10.2"'

   CREATE MEMBER [SampleCube].[Measures].[Percent of All Member] as
   '[Measures].[ActualSum] / ([Measures].[ActualSum], [Time].[All Time]), 
   format_string="Percent10.2"'

   CREATE MEMBER [SampleCube].[Measures].[Percent of All Dimensions] as
   '[Measures].[ActualSum] / ([Measures].[ActualSum], [Time].[All Time], [Geography].[All Geography], [Products].[All Products]), 
   format_string="Percent10.2"'

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

The code below can be submitted in any SAS 9 session that has access to a SAS^® OLAP Server with the SampleCube defined. You must update the macro variables before submitting the code.

The code below only demonstrates the contribution to the parent level. For other contribution types, click the Details tab in this sample.

%let SERVER = server.company.com; * The machine or IP Address where your SAS OLAP Server process is executing;
%let PORT = 5451; *The port number for the SAS OLAP Server process;
%let USER = sasdemo; * A User ID with Read and Read Metadata permissions to the SampleCube;
%let PASSWORD = sasdemo1; * The password for the above User ID;

proc sql;
  connect to olap (host="&SERVER" port=&PORT user="&USER" pass="&PASSWORD");

  create table temp as
    select * from connection to olap (

    with 
      member [Measures].[Percent of Contribution] as '[Measures].[ActualSum] / ([Measures].[ActualSum],[Time].currentmember.parent), format_string="Percent10.2"'

    select 
      {[Time].[All Time].[1993].children} on rows,
      {[Measures].[ActualSum], [Measures].[Percent of Contribution]} 
        on columns
        from [SampleCube]

    );

    select  YEAR label = 'Year',
         QUARTER label = 'Quarter',
       ACTUALSUM label = 'Sum of Actual Sales' format=Dollar12.2,
       'Percent of Contribution'n label = 'Percent of Contribution to Parent' format=Percent10.2
    from temp;
    disconnect from olap;
quit;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information