01-27-2017 03:29 AM
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
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
01-27-2017 04:34 AM
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.
01-27-2017 04:53 AM
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;
01-27-2017 07:06 AM
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:
forecast = probability / 100 * budget;
proc tabulate data=want;
var budget forecast;
tables probability all,
budget=' ' * (n='Nbr_of_Optys' sum='Tot_Budget' * f=dollar12.)
forecast=' ' * sum='Tot_Forecast'
As always, you can tinker with the format once you have it coming out with the right numbers.
01-27-2017 10:33 AM
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?