Modify Proc Tabulate Output

[sascode]

Hello!

I have a dataset as follows:
data have;
input sale_id year Month $ lease_type $ ;
datalines;
1 2010 Sep A
1 2010 Sep B
3 2010 Sep C
1 2010 Oct A
2 2010 Oct B
1 2011 Sep A
2 2011 Sep B
3 2011 Sep C
1 2011 Oct A
2 2011 Oct B
;
run;

In need to create summarized report and to do that i am using proc Tabulate as follows:

proc sort data = have;
by sale_id;
run;

data have1;
set have;
by sale_id;
if first.sale_id then uniq_id = 1;
else uniq_id = 0;
run;

proc tabulate data = have1 ;
class year month lease_type ;
var uniq_id;
table year * month all , lease_type * uniq_id = ' ' *( N = 'Sales' sum = 'Unique Sale_ID'*f= 10.
COLPCTN = 'Sales(%)'*F=6.1
COLPCTSUM<uniq_id> = 'Unique Sale_ID(%)'*F=6.1
)

uniq_id= 'Total' *(N='Total Sales' sum = 'Total Unique Sales_Id'*f= 10.
COLPCTN = 'Sales(%)'*F=6.1
COLPCTSUM<uniq_id> = 'Unique Sales_ID(%)'*F=6.1
) ;
keylabel all = "Not Corrected Total";

run;

The output is coming correctly, except, last total row(Not Corrected Total %),
where, i do not need total percentage (100 %) but i need the percentage calculates with grand total in very end;
for instance in A lease type , in last total row i need to have as follows:
Sales Unique_Sale_Id  Sales(%) Unique_Sale_Id(%)

4             1                      40                10

The question is what i need to change in this tabulate procedure?
Also, would this have been done without creating distinct id indicator?

[ballardw]

Please instead of just stating specific values such as this:

but i need the percentage calculates with grand total in very end;
for instance in A lease type , in last total row i need to have as follows:
Sales Unique_Sale_Id  Sales(%) Unique_Sale_Id(%)

4             1                      40                10

Provide a bit clearer description such as, I think, that you want a ROW pct total for Sales %. You need to walk us through exactly which numerator and denominator you need. Also, Proc Tabulate does not use the result of another statistic in another values calculation by user specification. So you can't use the "n" from Total sales in the row dimension directly.

You won't be able to do that at all with your current layout out as that would require that the ALL with different statistics cross the other statistics and SAS won't allow that.

Closest to what I think you request be a second table with the desired summary

[sascode]

Hi,
i believe i have specified , i need the percentages related to grand totals , which in this example are 10(Total sales) and 3 (total unique sales id) .
Therefore, in last total row, for lease type A, it would be 4 sales/10 = 0.4 or 40%,
and 1 unique sale id /3 = 0.33 or 33%.
Thank you for your suggestion .

[ballardw · Accepted solution]

---

@sascode wrote:

Hi,
i believe i have specified , i need the percentages related to grand totals , which in this example are 10(Total sales) and 3 (total unique sales id) .
Therefore, in last total row, for lease type A, it would be 4 sales/10 = 0.4 or 40%,
and 1 unique sale id /3 = 0.33 or 33%.
Thank you for your suggestion .

---

The bold part above does not match your initial description which placed a 10%.

This would be my approach barring a complete redesign of the data (and not sure what I might do not my report).

data have;
input sale_id year Month $ lease_type $ ;
datalines;
1 2010 Sep A
1 2010 Sep B
3 2010 Sep C
1 2010 Oct A
2 2010 Oct B
1 2011 Sep A
2 2011 Sep B
3 2011 Sep C
1 2011 Oct A
2 2011 Oct B
;
run;


proc sort data = have;
by sale_id;
run;

data have1;
set have;
by sale_id;
if first.sale_id then uniq_id = 1;
else uniq_id = 0;
run;

 

proc tabulate data = have1 ;
class year month lease_type ;
var uniq_id;
table year * month  , 
      lease_type * uniq_id = ' ' 
         *( N = 'Sales' sum = 'Unique Sale_ID'*f= 10.
            COLPCTN = 'Sales(%)'*F=6.1
            COLPCTSUM<uniq_id> = 'Unique Sale_ID(%)'*F=6.1
          )

      uniq_id= 'Total' *(N='Total Sales' sum = 'Total Unique Sales_Id'*f= 10.
      COLPCTN = 'Sales(%)'*F=6.1
      COLPCTSUM<uniq_id> = 'Unique Sales_ID(%)'*F=6.1
      ) 
    ;
table all  , 
      lease_type * uniq_id = ' ' 
         *( N = 'Sales' sum = 'Unique Sale_ID'*f= 10.
            RowPCTN = 'Sales(%)'*F=6.1
            PCTSUM = 'Unique Sale_ID(%)'*F=6.1
          )

      uniq_id= 'Total' *(N='Total Sales' sum = 'Total Unique Sales_Id'*f= 10.
      RowPCTN = 'Sales(%)'*F=6.1
      PCTSUM = 'Unique Sales_ID(%)'*F=6.1
      ) 
    ;
keylabel all = "Not Corrected Total";

run;

Something minor to consider would be using a numerical month variable with custom format to display the month name/abbreviation so the month values column sorts correctly. I find seeing Oct before (above) Sep as a bit jarring.

[sascode]

Hi , yes,
you are correct, it should be 33%,
i did run your code, it generates the desired total row.
I would like to ask, is the total row displayed separately because of second table statement?
I appreciate your help.

[ballardw]

---

@sascode wrote:
Hi , yes,
you are correct, it should be 33%,
i did run your code, it generates the desired total row.
I would like to ask, is the total row displayed separately because of second table statement?
I appreciate your help.

---

Yes.

If you think you really need the appearance of single table you may be able to do this with Proc Report which has the compute block that will allow you to do some pretty interesting summaries but gets very picky about the column orders for doing calculations with statistic results.

Or for the truly adventurous you could create an output data set from Tabulate with the above code, do some serious massaging to get all the values in a different format and then display them. I would only go that route if I knew I was going to do this frequently as the data structures created by Tabulate take a bit of work. For instance there is going to be a variable that tells you which of Table1 or Table2 the values are in since the code creates 2 tables.

[sascode]

OK, clear.

Thank you again.