BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vicky07
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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=).

 

View solution in original post

5 REPLIES 5
andreas_lds
Jade | Level 19

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
Tourmaline | Level 20

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:

 

Udklip.PNG

 

vicky07
Quartz | Level 8

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
Super User

@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
Quartz | Level 8
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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 675 views
  • 3 likes
  • 4 in conversation