BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ramya_sahu
Obsidian | Level 7

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_optysTot_BudgetTot_Forcast
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
 361$1,103,894,670

 

Thank you in advance..

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

make_fake_data.png

 

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.

use_temp_variable.png

 

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

View solution in original post

11 REPLIES 11
Ksharp
Super User

Total_Forcast=Total_Budget.SUM    *Probability/100;

ramya_sahu
Obsidian | Level 7

i am getting below error message:

 

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

Yavuz
Quartz | Level 8

 


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
Obsidian | Level 7

Thanks Yavuz.

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

Cynthia_sas
SAS Super FREQ
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
Obsidian | Level 7

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
SAS Super FREQ
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
Obsidian | Level 7

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
SAS Super FREQ

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

make_fake_data.png

 

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.

use_temp_variable.png

 

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
Obsidian | Level 7

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
SAS Super FREQ
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

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
  • 11 replies
  • 1672 views
  • 1 like
  • 4 in conversation