Need help with proc tabulate

[vicky07]

Hello,

I need help with generating a repot in tabulate form. Below is my input data and  the format of the output that I am looking to generate. I tried few iterations using proc tabulate but could get the expected output. Any help is much appreciated. Thank you in advance for your time and attention!!

Code:

Data sample;
input P_date date9. week_day $ Merchant $ ct pin_ct avg_days;
cards;
02Jan2020 Saturday CCL 77 23 13.987
02Jan2020 Saturday MMY 7034 290 17.0566
02Jan2020 Saturday HGY 897 156 56
05Jan2020 Tuesday CCL 976 85 19.874
05Jan2020 Tuesday MMY 6756 442 89.9843
05Jan2020 Tuesday HGY 900 11 15.77
05Jan2020 Tuesday JHU 8900 654 78.7655
;
run;

Data Sample2;
Set sample;
format p_date date9.;
run;

Output:

		Product Sale Report
		CCL				MMY				HGY				JHU
P_dt	Week_day	ct	Pin Ct	Pin %	Avg Days	ct	Pin Ct	Pin %	Avg Days	ct	Pin Ct	Pin %	Avg Days	ct	Pin Ct	Pin %	Avg Days
02JAN2020	Saturday	77	23	29.87%	13.987	7,034	290	4.12%	17.0566	897	156	17.39%	56	-	-	-	-
05JAN2020	Tuesday	976	85	8.71%	19.874	6,756	442	6.54%	89.9843	900	11	1.22%	15.77	8,900	654	7.35%	78.7655
Total		1,053	108	10.26%	16.9305	13,790	732	5.31%	53.52045	1,797	167	9.29%	35.885	8,900	654	7.35%	78.7655

[andreas_lds]

I don't think that proc tabulate can create the output you want. The procedure is really useful, when you have to aggregate date, but it seems that you are beyond that step. You could give proc report a try:

proc report data=sample;
   columns P_Date week_day merchant, (ct pin_ct avg_days);
   define P_date / group format=date9.;
   define week_day / group;
   define Merchant / across order=data;
   define ct / sum;
   define pin_ct / sum;
   define avg_days / sum;
   
   rbreak after / summarize;
run;

[PeterClemmensen]

Here is the Proc Tabulate code

proc tabulate data = temp;
   class P_date week_day Merchant;
   var ct pin_ct avg_days;
   tables P_date*week_day all, Merchant='Product Sale Report'*(ct*sum='' 
                                                               pin_ct*sum='' 
                                                               pin_ct='pin %'*pctsum<ct>='' 
                                                               avg_days*sum='');
   format P_date date9.;
run;

Result:

[vicky07]

Thank you for your response!

Couple questions..

How to change the format of ct & pin_ct from decimal to comma separated without decimals?

In the total row(ALL) I want to avg_days column  to be weighted average. Right now it is doing sum on avg_days.

Thanks.

[ballardw · Accepted solution]

---

@vicky07 wrote:

Thank you for your response!

Couple questions..

How to change the format of ct & pin_ct from decimal to comma separated without decimals?

---

I assume you mean the statistics in the table related to those variables.

You can override the default format for statistics in the body of the table with <statistic keyword>*f =formatspecification.

So something like this:

proc tabulate data = temp;
   class P_date week_day Merchant;
   var ct pin_ct avg_days;
   tables P_date*week_day all, 
         Merchant='Product Sale Report'*(ct*sum='' *f = comma6.0 
                                         pin_ct*sum=''*f=comma6.0 
                                         pin_ct='pin %'*pctsum<ct>='' 
                                         avg_days*sum='');
   format P_date date9.;
run;

---

@vicky07 wrote:

In the total row(ALL) I want to avg_days column  to be weighted average. Right now it is doing sum on avg_days.

---

Weighted by what? To apply a weight you would have to have a Variable holding the weight for each observation. Your example data does not show any such variable. To apply a weight to only selected variables you would list those variables on a separate VAR statement and use the weight option.

proc tabulate data = temp;
   class P_date week_day Merchant;
   var ct pin_ct;
   var avg_days / weight= weightvariablename;
   tables P_date*week_day all, 
         Merchant='Product Sale Report'*(ct*sum='' *f = comma6.0 
                                         pin_ct*sum=''*f=comma6.0 
                                         pin_ct='pin %'*pctsum<ct>='' 
                                         avg_days*sum='');
   format P_date date9.;
run;

You really want to read the documentation for how 0, missing or negative values for weight variables affect results to make sure you understand the results. Additional options will need to be set if doing quantiles and variance or standard deviation (VARDEF=).

[vicky07]

I wanted the weighted average by "ct" variable and I included it in the VAR. Sorry, I should have been more clear.

Thanks again for your help!