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
Probability | Nbr_of_Optys | Tot_Budget | Tot_Forecast |
100 | 7 | $171,675,000 | $171,675,000 |
90 | 4 | $205,000,000 | $184,500,000 |
70 | 8 | $264,000,000 | $184,800,000 |
50 | 20 | $127,040,000 | $63,520,000 |
30 | 3 | $2,450,000 | $735,000 |
10 | 319 | $333,729,670 | $33,372,967 |
361 | 1103894670 | 638602967 |
Please help.
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.
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;
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.
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?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.