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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.