Hi:
It's not entirely clear to me what you want summarized. I don't see a sort for the QNUMMESE variable so I suspect you're getting an error on the BY statement in PROC REPORT. Also not sure what you're doing with the DATA _NULL_ step. It looks like you're building assignment statements for PROC REPORT, but I don't see your macro variable being used.
I also don't understand why SUMA and _PERCENT are defined as GROUP usage, but if you want SUMA and/or _PERCENT to be summarized --- or just SUMA summarized so that it can be used in the division on the break, then it can't be a usage of GROUP. It has to be a usage of ANALYSIS with a default statistic of SUM. And In running a test (thanks for data and code), but focusing only on QNUMMESE of 36 to illustrate my point compare these 2 partial outputs:
The top report was produced with the original program with all the usages of GROUP. Note that for QNUMMESE=36 and IPO_WINDOW=-2, when you have GROUP usage, the value for _PERCENT is 13.4% and the value for SUMA is 40. However, you have 3 rows for this group where QNUMMESE=36 and IPO_WINDOW=-2 -- note that in the second changed program, I reveal all 3 rows because I changed the usage to ORDER, which does not collapse the rows and you can see that the _PERCENT value is the same on all 3 rows and the SUMA value is the same on all 3 rows. If you add up the numbers on all 3 rows for SUMA, it would be 120...not 40. But my guess is that whatever process created the original data in your program repeated these values for every row where QNUMMESE=36 and IPO_WINDOW=-2. A Usage of GROUP will indeed show you only 1 value for _PERCENT and only 1 value for SUMA, but the GROUP usage also prevents your summary line from getting a total for SUMA. Is your calculation supposed to be performed on the summary line? If so, then you've got to figure out a different way to display SUMA on the report but still have it summarized on the BREAK line. You can do this easily by post-processing your data, so that the SUMA value only appears once for each unique combination of QNUMMESE and IPO_WINDOW, and that would look something like this for the summary:
Again, just isolated the test to QNUMMESE=36, but you can see I did get a summary line and the same values for SUMA that you showed in your original report. However, called the original DATA step file WORK.FAKEDATA and then post-processed that file to create WORK.FAKEDATA2 and used that in a changed PROC REPORT, as shown below (not repeating your original code or data creation).
proc sort data=fakedata;
by qnummese ipo_window;
run;
data fakedata2;
set fakedata;
by qnummese ipo_window;
if first.ipo_window = 0 then suma=.;
run;
title 'Changed Data and Changed Program';
proc report data=fakedata2 nowd center split="*" ;
by qnummese;
where qnummese = 36;
column qnummese ipo_window _percent suma (ipo_drawn, (count mkpi)) ;
define qnummese / group ;
define ipo_window / group ;
define _percent / f=percent9.1 group ;
define suma / analysis ;
define ipo_drawn / order across 'IPO drawn' left ;
define count / analysis sum 'N' ;
define mkpi / computed f=percent9.1 'KPI' style(column)={background=fpcta.};
compute mkpi;
_c6_=_c5_/suma.sum;
_c8_=_c7_/suma.sum;
_c10_=_c9_/suma.sum;
_c12_=_c11_/suma.sum;
_c14_=_c13_/suma.sum;
_c16_=_c15_/suma.sum;
_c18_=_c17_/suma.sum;
_c20_=_c19_/suma.sum;
_c22_=_c21_/suma.sum;
_c24_=_c23_/suma.sum;
_c26_=_c25_/suma.sum;
_c28_=_c27_/suma.sum;
_c30_=_c29_/suma.sum;
_c32_=_c31_/suma.sum;
_c34_=_c33_/suma.sum;
_c36_=_c35_/suma.sum;
_c38_=_c37_/suma.sum;
_c40_=_c39_/suma.sum;
_c42_=_c41_/suma.sum;
_c44_=_c43_/suma.sum;
_c46_=_c45_/suma.sum;
_c48_=_c47_/suma.sum;
_c50_=_c49_/suma.sum;
_c52_=_c51_/suma.sum;
_c54_=_c53_/suma.sum;
_c56_=_c55_/suma.sum;
_c58_=_c57_/suma.sum;
_c60_=_c59_/suma.sum;
endcomp;
break after qnummese / summarize;
run;
I hope that gives you some idea of how to get a summary line. I assume the numbers for SUMA are what you want, but my assumption could have been wrong. I did not cause _PERCENT to be summarized because it seemed more important to focus on SUMA, but if you want _PERCENT to be summarized at the break, you'll have to do the same thing for _PERCENT in the processing step that I did for SUMA.
Cynthia
... View more
