BookmarkSubscribeRSS Feed
JithinJoe
Fluorite | Level 6

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 

  

ProbabilityNbr_of_OptysTot_BudgetTot_Forecast
1007$171,675,000$171,675,000
904$205,000,000$184,500,000
708$264,000,000$184,800,000
5020$127,040,000$63,520,000
303$2,450,000$735,000
10319$333,729,670$33,372,967
    
 3611103894670638602967

 

 

Please help. 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Shmuel
Garnet | Level 18

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;
Astounding
PROC Star

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.

ballardw
Super User

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?

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 935 views
  • 0 likes
  • 5 in conversation