Hi All,
I am preparing a report. In which i calcualted Total Forecast using formula probability*Total budget.
My last row is summary row where i dont have any prbability value. so, i am not getting Total value for Total forcast.
I want to print sum of total forecast i last cell.
Can some one help me out here?
Code:
proc report data=olympics;
where probability____<>0;
column probability____=Probability probability____=Nbr_of_optys Total_Media_Value=Total_Budget
Total_Forcast ;
define Probability/group descending 'Probability';
define Nbr_of_optys/n 'Nbr_of_optys';
define Total_Budget/sum 'Tot_Budget' format=dollar15.;
define Total_Forcast/computed 'Tot_Forcast' format=dollar15. ;
compute Total_Forcast;
Total_Forcast=Total_Budget*Probability/100;
endcomp;
rbreak after/summarize;
run;
Output:
Probability | Nbr_of_optys | Tot_Budget | Tot_Forcast |
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 | $1,103,894,670 |
Thank you in advance..
Hi, Have you used temporary variables with PROC REPORT? Have you read about them? Temporary variables are automatically retained. So within a COMPUTE block, you can increment a temporary variable and the value will be retained across the creation of all report rows.
Using some fake data
I wrote a program that illustrates the problem (the formula resulting in missing on the summary row) and then showed how to use a temporary variable to capture the total for the column and use it at the end of the report.
You need to be sure to test the value of the _BREAK_ variable, which is described in the PROC REPORT documentation, so that when the value of _BREAK_ is _RBREAK_, you are able to place the saved value into the correct cell.
cynthia
Total_Forcast=Total_Budget.SUM *Probability/100;
i am getting below error message:
Error: The variable type of TOT_BUDGET.SUM IS invalid in this context.
DATA have;
INPUT Probability Nbr_of_optys Tot_Budget Tot_Forcast;
DATALINES;
100 7 171675000 171675000
90 4 205000000 184500000
70 8 264000000 184800000
50 20 127040000 63520000
30 3 2450000 735000
10 319 333729670 33372967
;
run;
PROC SQL;
CREATE TABLE WORK.have1 AS
SELECT /* Probability */
(.) AS Probability,
/* Nbr_of_optys */
(SUM(t1.Nbr_of_optys)) AS Nbr_of_optys,
/* Tot_Budget */
(SUM(t1.Tot_Budget)) AS Tot_Budget,
/* Tot_Forcast */
(.) AS Tot_Forcast
FROM WORK.have t1
GROUP BY (CALCULATED Probability),
(CALCULATED Tot_Forcast);
QUIT;
data final;
set have have1;
run;
Thanks Yavuz.
i want the result using proc report. I know its possible in proc report, but i am missing somewhere.
HI Synthia,
I have attached the sample dataset.
Output table should be grouped by Probability.
A Computed variable total_forcast to be created using formula total_media_value*probability.
At the end need to summarize numerical variables exceplt probability.
I have given the sample output in my question, where i am able to create everything but i am not getting total of computed variable.
I can do same thing with Proc SQL & get the required result. But i want to make it through proc report.
Your help here will be highly appreciated.
Thanks
Yes cynthia, you are absolutly correct.
I want to NOT use this formula at the end of the row for the variable total_forcast , but instead want to summarize what has been calculated on all the other rows.
And i want the ods output as PDF.
Thanks
Hi, Have you used temporary variables with PROC REPORT? Have you read about them? Temporary variables are automatically retained. So within a COMPUTE block, you can increment a temporary variable and the value will be retained across the creation of all report rows.
Using some fake data
I wrote a program that illustrates the problem (the formula resulting in missing on the summary row) and then showed how to use a temporary variable to capture the total for the column and use it at the end of the report.
You need to be sure to test the value of the _BREAK_ variable, which is described in the PROC REPORT documentation, so that when the value of _BREAK_ is _RBREAK_, you are able to place the saved value into the correct cell.
cynthia
Hi Cynthia,
Thanks a lot. It works perfectly.
As i am new to SAS, i dont know about creation of these temporary variable concept.
I am learning new things from you.
I have some doubts here.
1. _Break_ & _RBREAK_ are two variables which created during PDV?
2. if yes, both must have writing some values during PDV, then why you have written '_RBEAK_' with in the quote as text?
if possible please share some links or documentation on creation of temporary variable and understanding _Break_ & '_RBREAK_' concepts.
Thanks
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.