topic Re: how to create summary Report using proc tabulate in SAS Programming

how to create summary Report using proc tabulate

JithinJoe — Fri, 27 Jan 2017 08:29:55 GMT

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.

Re: how to create summary Report using proc tabulate

RW9 — Fri, 27 Jan 2017 09:34:11 GMT

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.

Re: how to create summary Report using proc tabulate

Shmuel — Fri, 27 Jan 2017 09:53:41 GMT

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;

Re: how to create summary Report using proc tabulate

Astounding — Fri, 27 Jan 2017 12:06:01 GMT

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.

Re: how to create summary Report using proc tabulate

ballardw — Fri, 27 Jan 2017 15:33:59 GMT

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?