Hi:
I now understand that the first "total" line is actually in your data and is not being calculated. Also, it appears, from your other information that you want the total number at the top to appear on the report and the first bucket number to appear on the report, but you do NOT want those numbers to be included in the total at the bottom for each TYPE, and that you want the number from that data on the TotalBal line for Amount to be used to calculate the Percent. That means your data is more structured like this (shown with only 2 TYPES):
data fakedata;
length Bucket $15;
infile datalines dlm=',' dsd;
input Type $ ordvar Bucket count amount Fcst;
return;
datalines;
Type 1,1,TotalOut, 100, 1000, .
Type 1,2,Bucket1, 20, 40, 100
Type 1,3,Bucket2, 10, 100, 200
Type 1,4,Bucket3, 20, 150, 170
Type 1,5,Bucket4, 10, 200, 200
Type 1,6,Bucket5, 20, 300, 100
Type 1,7,Bucket6, 20, 19, 50
Type 2,1,TotalOut, 150, 1500, .
Type 2,2,Bucket1, 25, 140, 200
Type 2,3,Bucket2, 15, 120, 300
Type 2,4,Bucket3, 25, 156, 176
Type 2,5,Bucket4, 15, 240, 240
Type 2,6,Bucket5, 25, 360, 230
Type 2,7,Bucket6, 25, 22, 55
;
run;
Since I didn't know how you were going to make sure that TotalOut or TotalBal at the top was going to be on the top, I added a helper variable called "ORDVAR" to my fake data, so that the buckets would stay in order.
You could calculate everything in a DATA step program, but if your data are already summarized, as shown in the data you posted, then once you get the TotalOut or TotalBal line added into the data, it would look like my FAKEDATA above.
So, conceptually, If I understand correctly, you want something like this based on the data:
To do that, you will need to make some helper variables in a DATA step, so that PROC REPORT can do the Total line (in blue) the way you want. There already is one helper variable called ORDVAR in the data. You will basically need a "shadow" variable for each column on the report: COUNT, AMOUNT, FCST and DIFF. In my code below, I name these helper variables USECOUNT, USEAMOUNT, USEFCST and USEDIFF. Remember that ORDVAR=1 is the TotalOut row and ORDVAR=2 is the Bucket1 row in the data. Basically, all the helper variables are assigned missing for the first 2 rows and use the regular variable values on the other rows.:
data finaldata; set fakedata; if ordvar in (1,2) then do; usecount = .; useamount=.; useFcst = .; usediff = .; end; else do; usecount=count; useamount=amount; useFcst = Fcst; usediff = useFcst - useamount; end; run;
Now with the help of PROC REPORT, and showing all the regular variables and helper variables, you have something like this:
The final PROC REPORT step that used the FINALDATA data set with the helper variables is here:
proc sort data=fakedata; by type ordvar; run; ods html file='c:\temp\finaldata1.html'; proc print data=finaldata; title '1) what does final data look like with all helper variables'; var type ordvar bucket usecount count useamount amount usefcst fcst usediff; sum usecount useamount usefcst usediff; run; ods html close; options missing = ' ' nobyline; title; footnote; ods html file='c:\temp\alltype.html'; ods tagsets.excelxp file='c:\temp\alltype.xml' style=htmlblue options(sheet_interval='none' sheet_name='All Types');
proc report data=finaldata style(header)={background=darkblue foreground=white fontweight=bold vjust=b} style(summary)={background=lightblue foreground=black fontweight=bold}; title1 '2) Final Report which hides helper columns and shows desired sums for Bucket 2-6'; column type ordvar bucket usecount count useamount amount pctbal usefcst fcst usediff diff; by type; define type / order page noprint; define ordvar / order noprint; define bucket /order order=data style(column)={fontweight=bold}; define usecount / sum noprint; define count / sum 'Count'; define useamount / sum noprint; define amount / sum f=comma14. style(column)={tagattr='#,###,###,##0'}; define pctbal / computed f=percent9.2 '%/of/Balance'; define usefcst / sum noprint; define fcst / sum f=comma14. style(column)={tagattr='#,###,###,##0'}; define usediff / sum noprint; define diff / 'Difference' computed f=comma14. style(column)={tagattr='#,###,###,##0'}; compute before ordvar; if ordvar = 1 then do; ** use HOLDAMT as helper var for division of pctbal; holdamt = amount.sum; end; endcomp; compute ordvar; if ordvar = 1 then call define(_row_,'style','style=Header{background=lightblue foreground=black fontweight=bold}'); endcomp; compute diff; if ordvar ne 1 then diff = fcst.sum - amount.sum; endcomp; compute pctbal; if ordvar ne 1 then do; if amount.sum gt . then pctbal = (amount.sum / holdamt); end; endcomp; compute after type; Bucket = 'Total'; count.sum = usecount.sum; amount.sum = useamount.sum; if useamount.sum gt . then pctbal = useamount.sum / holdamt; fcst.sum = usefcst.sum; diff = usediff.sum; endcomp; break after type / summarize; compute before _page_ / style=Header{background=darkblue foreground=red fontsize=12pt fontweight=bold}; line type $10.; endcomp; run; ods _all_ close; options byline;
I think this comes close to what you described to me in a separate message. Otherwise, I think it is a good approximation.
cynthia
... View more
