turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- how to create summary Report using proc tabulate

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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 **

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JithinJoe

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JithinJoe

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:

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JithinJoe

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?