DATA Step, Macro, Functions and more

how to create summary Report using proc tabulate

Reply
Occasional Contributor
Posts: 8

how to create summary Report using proc tabulate

Hi, I need to use proc tabulate for creating a summary report, I’m having issues getting report to come out as desired, please do have check, bellow is the desired O/P.  Sample data is attaed as  datastep version, attached with this. Please do have a look. 

The variables used in the output table is  

probability= Probability

Nbr_of_Optys = No of deals for probability level

Tot_Budget  =sum( Total media values for Probability level)

Tot_Forecast = Tot_ Budget * Probability /100

 

The desired output 

  

ProbabilityNbr_of_OptysTot_BudgetTot_Forecast
1007$171,675,000$171,675,000
904$205,000,000$184,500,000
708$264,000,000$184,800,000
5020$127,040,000$63,520,000
303$2,450,000$735,000
10319$333,729,670$33,372,967
    
 3611103894670638602967

 

 

Please help. 

Attachment
Super User
Super User
Posts: 7,997

Re: how to create summary Report using proc tabulate

Posted in reply to JithinJoe

Post test data in the form of a datastep.  Post code you have tried.  Post what " I’m having issues getting report to come out as desired" issues you are having.  Also explain why " I need to use proc tabulate for creating a summary report" - summaries can be done in any number of ways.  Me, I am personally not fond of the tabulate procedure, so I would do this in a datastep, get it looking exactly as I want, then proc report that data out. 

Trusted Advisor
Posts: 1,586

Re: how to create summary Report using proc tabulate

I agree with every remark of @RW9.

Your report is one dimmension (class=probabilty) while proc tabulate is well used with two or three dimensions;

 

I did not get, within my code, the results (amounts) you expect.

I have probably misunderstand your formulas, but you may use the code as

skilton to addapt to your needs:

proc summary data=OLYMPICS nway missing;  /* sum( Total media values for Probability level) */
   class probability;
   var Total_Property_s__Budget;
   output out=ol_sum(drop=_type_) sum=tot_budget;
run;

data ol2rep;
 set ol_sum(rename=(_freq_=Nbr_Of_Optys));
     tot_forcast = tot_budget * probability /100;
run;
proc sort data=ol2rep; by descending probability; run;
proc print data=ol2rep; 
    var probability Nbr_Of_Optys Tot_Budget Tot_Forcast;
    sum Nbr_Of_Optys Tot_Budget Tot_Forcast;
run;
Super User
Posts: 5,518

Re: how to create summary Report using proc tabulate

Posted in reply to JithinJoe

Since Tot_Forecast depends on multiplying two numeric variables, you will need to do at least a little processing before PROC TABULATE.  PROC TABULATE permits just one analysis variable per cell of the table.

 

Here is an (untested) program that should give you enough to work with:

 

data want;

set have;

forecast = probability / 100 * budget;

run;

 

proc tabulate data=want;

class probability;

var budget forecast;

tables probability all, 

  budget=' ' * (n='Nbr_of_Optys' sum='Tot_Budget' * f=dollar12.)

  forecast=' ' * sum='Tot_Forecast'

  ;

run;

 

As always, you can tinker with the format once you have it coming out with the right numbers.

Super User
Posts: 11,343

Re: how to create summary Report using proc tabulate

Posted in reply to JithinJoe

Example data really only needs the variables of interest. If using a program to generate the datastep code suggest making a set using KEEP option for the variables needed.

 

A couple of concerns: First you do not have a variable Tot_ Budget in the data. I would guess that you meant Total_Property_s__Budget_Currenc.

 

Second is since you provided variables related to currency type is there an implication that if there are different currencies that there needs to be some standardization to a common currency with the full data?

 

Ask a Question
Discussion stats
  • 4 replies
  • 118 views
  • 0 likes
  • 5 in conversation