Summarize computed variable

[ramya_sahu]

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

[Ksharp]

Total_Forcast=Total_Budget.SUM    *Probability/100;

[ramya_sahu]

i am getting below error message:

Error: The variable type of TOT_BUDGET.SUM IS invalid in this context.

[Yavuz]

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;

[ramya_sahu]

Thanks Yavuz.

i want the result using proc report. I know its possible in proc report, but i am missing somewhere.

[Cynthia_sas]

Hi:
It is hard to envision what your data looks like from the PROC REPORT step because you have PROBABILITY defined as both a GROUP and an ANALYSIS variable. That is odd. Also, you have a variable named probability, but are there 3 or 4 underscores after the variable name.

You show your code but you don't show the desired ODS output destination. And, you don't show ALL your ODS statements. the other thing is that it will be very hard for people to run a program to test what is going wrong because you did not provide any data. The challenge with a question like this is that every person who reads your post will need to make some "fake" data, if they are inclined to help and the "fake" data they make may or may not correspond to the data you have.

You are using the alias technique in your program. and when you use an ALIAS, you can refer to the item in your COMPUTE block by the simple alias name. So your compute block seems OK. However, I suspect, but without any data to test can't be sure that your formula is failing because the value of the alias Probability should be blank on the RBREAK row, which means that you are multiplying a value by a blank (missing) which should result in Missing and a note in the log about missing values being generated.

cynthia

[ramya_sahu]

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

[Cynthia_sas]

Hi:
Most people on the Forum avoid XLSX files and prefer to have a DATA step program that would read the data into a data set using DATALINES and INFILE/INPUT. That avoids any issues of opening XLSX files and reading the file if you do not have the right PC File Server or SAS/ACCESS for Excel plug ins.

Also, you did NOT say what type of ODS output you wanted.

In your first posting, you said that you "want to print sum of total forecast in last cell." and now you say that you want to "to summarize numerical variables except probability."

But all PROC REPORT can do, by default, is execute your formula for Total_Forcast on the last row, which means multiplying the Total_budget number by Probability. Since Probability is a defined as a GROUP variable it will be MISSING or BLANK on the last row, that means your calculation for the TOTAL_FORCAST variable will be like saying at the summary line:
Total_Forcast = Total_Budget*missing/100;

You seem to imply that you want to NOT use this formula at the end of the row, but instead want to summarize what has been calculated on all the other rows? This seems to me to require the use of temporary variables with PROC REPORT.

cynthia

[ramya_sahu]

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 

[Cynthia_sas · Accepted solution]

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

[ramya_sahu]

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

[Cynthia_sas]

Hi, sorry, I did not see this until today.

The name of the variable is _BREAK_ -- it can have different values, depending on the type of break that is happening -- _RBREAK_ and _PAGE_ are two of the values that _BREAK_ can have. Since _BREAK_ is a character variable, in any IF statement, I must compare _BREAK_ to a character string. So that is why the test is
IF _break_ = '_RBREAK_' -- because '_RBREAK_' is the VALUE of the _BREAK_ automatic.

PROC REPORT is NOT the DATA step. It should NOT be confused with the DATA step. PROC REPORT does NOT have a Program Data Vector (PDV).

For more on _BREAK_ processing and how it works, keep an eye out for my SGF 2017 paper, entitled: "Go Ahead and _BREAK_-down: Advanced COMPUTE Block Examples" paper #SAS431-2017.

cynthia