How to Add Additional Prior Month Total in PROC Report

Cynthia_sas · Posted 02-09-2012 12:21 PM

Hi:

My idea is that it is not the a problem in the example code or previously posted examples (that used fake data) that your boss changed his mind.

What PROC REPORT code are you actually using? I would have expected that if you were going to use any of the concepts from the previous postings, you would have CHANGED the PROC REPORT code to suit your data. At this point, considering all the code that has been offered to you and the way your data seems to have changed and the way your report requirements seem to have changed, what has been posted in the past is almost certainly not relevant anymore.

I'm not sure what you mean when you say you need to "have prior month total across different region and all region report should be in different worksheet" -- depending on the level of granularity, you may need a separate PROC REPORT step for your "ALL REGION" report. And, one of the previous examples DID show how to have the prior month total for each region. Your initial posting was how to get an extra total line, then how to get an extra total line for each region with the prior month total for each region. Now it's not clear to me what you are asking for or how it relates to the data you posted, since the data you posted seems quite different from the example data.

And, when you say that the code is not working, what code? The previous posts had about 6 different examples illustrating PROC REPORT concepts with fake data. It makes sense to me that the code wouldn't work unless you were using data with the EXACT same structure as the fake data used in the code. And, it looks like your data is nothing like the fake data that was posted.

If you have a report, based on previous postings, that worked just fine and now your boss has changed his mind, it just means you have to rework your code to produce the report your boss wants. That may mean that some of the PROC REPORT concepts previously illustrated are no longer appropriate for what you want.

But since all you have done is post data and not shown any of your code or an example of what you want, it is hard to visualize what it is you do want or how it's related to or different from what's been previously posted. And it seems like it might be significantly different from any of the other examples.

If you really are stuck, with general PROC REPORT concepts and the way things work, then you might consider working with Tech Support -- but again, you will have to show them the real code that you've tried with your real data.

cynthia

c8826024 · Posted 02-09-2012 01:03 PM

I want to attached the report template but I have a difficulty to attach to my post. Here is my code:

%include '/opt/sas/Tablespace/mydirectory/SAS/ExcelXP.sas';

libname mylib '/opt/sas/Tablespace/mydirectory/SAS/';

ods path mylib.tmplmst(update) sashelp.tmplmst(read);

ods listing close;

ods tagsets.ExcelXP path='/opt/sas/Tablespace/mydirectory/' file="PP_Pay_MIS_&mname..xls";

ods tagsets.ExcelXP options(sheet_interval='bygroup' sheet_label='' suppress_bylines='yes' autofit_height='yes'

absolute_column_width='4,7.71,7.71,5,5.71,8,8,8,8,5.71,5.71,8,8.71,8.71,8.71,7.71');

proc report data=final nowd headline headskip

style(column)={font_size=1.5} style(header)={FONT_WEIGHT =bold font_size=1.5};

where proc_mm=&proc_mm.;

by country;

COLUMN process_dt p_dt ('Pre Charge-Off' a1 a2 a7 a9 a11 a_total) ('Post Charge-Off' b1 b2 b7 b9 b11 b_total) ab_total realcoamt;

define process_dt /group format=mmddyy8. noprint;

define p_dt /computed 'Process Date' left style(column)={background=white font_size=1.5};

define a1 /analysis sum format=dollar10. 'Agent OB Payment(Internal Placed)'

style(column)={background=lightyellow font_size=1.5 tagattr="$###,###"};

define a2 /analysis sum format=dollar10. 'Agent IB Payment(Internal Placed)'

style(column)={background=lightyellow font_size=1.5 tagattr="$###,###"};

define a7 /analysis sum format=dollar10. 'OCA Collected' style(column)={background=lightyellow font_size=1.5 tagattr="$###,###"};

define a9 /analysis sum format=dollar10. 'OCA PMT. Taken by PPE' style(column)={background=lightyellow font_size=1.5 tagattr="$###,###"};

define a11 /analysis sum format=dollar10. 'Agent IB PMT on Non-Placed Acct.' style(column)={background=lightyellow font_size=1.5 tagattr="$###,###"};

define a_TOTAL /analysis sum format=dollar10. 'Pre-Chgff Total/(Placed Acct. Only)'

style(column)={background=lightyellow font_size=1.5 tagattr="$###,###"};

define b1 /analysis sum format=dollar10. 'Agent OB Payment(Internal Placed)'

style(column)={background=lightblue font_size=1.5 tagattr="$###,###"};

define b2 /analysis sum format=dollar10. 'Agent IB Payment(Internal Placed)'

style(column)={background=lightblue font_size=1.5 tagattr="$###,###"};

define b7 /analysis sum format=dollar10. 'OCA Collected' style(column)={background=lightblue font_size=1.5 tagattr="$###,###"};

define b9 /analysis sum format=dollar10. 'OCA PMT. Taken by PPE' style(column)={background=lightblue font_size=1.5 tagattr="$###,###"};

define b11 /analysis sum format=dollar10. 'Agent IB PMT on Non-Placed Acct.' style(column)={background=lightblue font_size=1.5 tagattr="$###,###"};

define b_total /analysis sum format=dollar10. 'Post-Chgff Total/(Placed Acct. Only)'

style(column)={background=lightblue font_size=1.5 tagattr="$###,###"};

define ab_total /analysis sum format=dollar10. 'Grand Total/(Placed Acct. Only)'

style(column)={background=pink font_size=1.5 tagattr="$###,###"};

define realcoamt /analysis sum format=dollar10. 'Charge-Off' style(column)={background=orange font_size=1.5 tagattr="$###,###"};

rbreak after / ol summarize;

compute p_dt/char length=8;

if _break_='_RBREAK_' then p_dt='Total';

else p_dt=put(process_dt,mmddyy8.);

endcomp;

run;

quit;

ods tagsets.ExcelXP close;

c8826024 · Posted 02-09-2012 01:05 PM

Here is the template I want:

Pre Charge-Off

Post Charge-Off

Process Date

a1

a2

a7

a9

a11

a_total

b1

b2

b7

b9

b11

b_total

ab_total

realcoamt

01/01/12

01/02/12

01/03/12

01/04/12

01/05/12

01/06/12

01/07/12

01/08/12

01/09/12

01/10/12

01/11/12

01/12/12

01/13/12

01/14/12

01/15/12

01/16/12

01/17/12

01/18/12

01/19/12

01/20/12

01/21/12

01/22/12

01/23/12

01/24/12

01/25/12

01/26/12

01/27/12

01/28/12

01/29/12

01/30/12

01/31/12

Total

Prior Month Total

c8826024 · Posted 02-09-2012 01:07 PM

That will be one worksheet for one region (NA, UK, DE, The Global total). All reports will be in one workbook.

Cynthia_sas · Posted 02-09-2012 02:16 PM

Hi:

Several of the previous postings showed how to get the extra break line for prior month total. Look at the code examples that show the creation of the XTRA variable (xtra = catt(region,'XXX');) and then the break for the prior month total on the report with a BREAK AFTER XTRA. And then a few examples show a COMPUTE block to change the values on the extra break line.

The previous postings did show how to do this....several different ways.

I notice that you still have sheet_interval="bygroups" and one of the recommendations in the previous post was to stop using BYGROUPS and use PROC REPORT's page capability to get a separate sheet for every REGION in the posted example code.

For a separate sheet on the overall totals, you will probably need a separate PROC REPORT step.

It seems that you haven't really used any of the concepts that were discussed in prior postings. The way to get an EXTRA break line on the report the way you want is either to create an EXTRA fake break variable OR to pre-process and pre-summarize your data with other methods.

cynthia