topic How to Add Additional Prior Month Total in PROC Report in ODS and Base Reporting

How to Add Additional Prior Month Total in PROC Report

c8826024 — Tue, 21 Jun 2011 01:07:57 GMT

All,

I use PROC REPORT to generate a sales report by region like this:

Date sale cost
6/1/2011 300 200
6/2/2011 400 100
6/3/2011 500 400
6/4/2011 600 200
6/5/2011 150 70
6/6/2011 350 250
M-t-d total 2400 1220
Prior month total 5000 3000

The charllenge is how I can get prior month total in the report. I know I may
use COMPUTE block. Does anybody know how I can do it?

Re: How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Tue, 21 Jun 2011 02:41:00 GMT

Hi:
In order to put an "extra" break line onto your report, to hold the prior month totals, you would need to use an "extra" variable to be a break variable. Assuming that your prior month totals are in a different dataset than the MTD dataset, you would need to figure out what the correct values were -- and then probably create a macro variable from those values and then use the macro variables in a COMPUTE block.

The example program below creates some fake data and then shows the progression of how a PROC REPORT might work...from #1 -- where you use the "extra" breaking variable to put 2 summary lines at the bottom of the report. to #2, where you assign different numbers to the summary line created by the RBREAK AFTER statement. And, finally, to #3, where you use macro variables in your COMPUTE block to change the RBREAK AFTER summary line. In my example, the 2 %LET statements would be replaced by some other code to get the Prior Month totals from the dataset that contains that data -- possibly with PROC SQL or with a DATA step program.

cynthia
[pre]
** make some data -- a few obs for each day;
** also create XTRA variable to get second break line;
** make this variable the same value for every obs.;
data sales;
xtra = 1;
do date='01JUN2011'd to '07JUN2011'd;
sales = ranuni(0) * 500;
cost = sales * .35;
output;
sales = sales + (sales*.13);
cost = sales * .35;
output;
end;
format date mmddyy10.;
run;

ods listing close;
ods html file='c:\temp\mult_break_lines_report.html' style=sasweb;
proc report data=sales nowd;
title '1) Show 2 Break Lines -- initially totals will be same';
column xtra date showdate sales cost;
define xtra / group;
define date / group;
define showdate / computed style(column)={just=r};
define sales /sum f=comma10.2;
define cost / sum f=comma10.2;
break after xtra / summarize;
rbreak after / summarize;
compute showdate / character length=30;
showdate = put(date,mmddyy10.);
if upcase(_break_) = 'XTRA' then
showdate = 'XTRA Break Total';
else if _break_ = '_RBREAK_' then
showdate = '_RBREAK_ Break Total';
endcomp;
run;

proc report data=sales nowd;
title '2) Change Break Line Values with fake numbers';
column xtra date showdate sales cost;
define xtra / group;
define date / group;
define showdate / computed style(column)={just=r};
define sales /sum f=comma10.2;
define cost / sum f=comma10.2;
break after xtra / summarize;
rbreak after / summarize;
compute showdate / character length=30;
showdate = put(date,mmddyy10.);
if upcase(_break_) = 'XTRA' then
showdate = 'XTRA Break Total';
else if _break_ = '_RBREAK_' then
showdate = '_RBREAK_ Break Total';
endcomp;
compute after;
** assign arbitrary numbers to the _RBREAK_ summary line;
sales.sum = 8888.88;
cost.sum = 7777.77;
endcomp;
run;

** Create macro variables with %LET -- or use other ;
** methods to get these numbers;
%let priorsales = 2222.22;
%let priorcost = 1111.11;

proc report data=sales nowd;
title '3) Use Macro Variable Values for _RBREAK_ Line';
column xtra date showdate sales cost;
define xtra / group noprint;
define date / group noprint;
define showdate / computed 'Date'
style(column)={just=r} ;
define sales /sum f=comma10.2 'Sales';
define cost / sum f=comma10.2 'Cost';
break after xtra / summarize;
rbreak after / summarize;
compute showdate / character length=30;
showdate = put(date,mmddyy10.);
if upcase(_break_) = 'XTRA' then
showdate = 'M-T-D Totals';
else if _break_ = '_RBREAK_' then
showdate = 'Prior Month Totals';
endcomp;
compute after;
** use macro variables to provide the correct Prior Month values;
sales.sum = &priorsales;
cost.sum = &priorcost;
endcomp;
run;
ods _all_ close;
title; footnote;
[/pre]

Re: How to Add Additional Prior Month Total in PROC Report

c8826024 — Tue, 21 Jun 2011 14:29:55 GMT

Thanks Cynthia. But my data is far more complicate. My report will be broken up by region. It will be difficult to define the macro variables by different regions.

Re: How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Tue, 21 Jun 2011 19:28:49 GMT

Hi:
Using Macro variables is not the ONLY technique -- it was just the one I used to illustrate the data that you posted.

Without knowing the structure of YOUR data, I have made some fake data for 2 regions. Then I made a fake "pastmonths" table with only a few months of summary information. Then using a MERGE, I made a new data file, where the summary info for the previous month was present only on the first observation for each region. This means that the summary in PROC REPORT will have the information from the MERGE available in a COMPUTE block. The PROC REPORT step below is almost exactly like what I previously posted. The bulk of the "new" stuff is the data manipulation/MERGE to get the structure correct. After you understand how PROC REPORT is operating, you can use NOPRINT on the columns for PRIORSALES and PRIORCOST to hide those columns on the final report.

cynthia
[pre]
data sales;
infile datalines dlm = ',' dsd;
input region $ date: mmddyy10. sales cost;
** subtract 1 from month to get prior month for merge;
month = month(date)-1;
year = year(date);
return;
datalines;
"East","06/01/2011",142.53,114.89
"East","06/01/2011",349.52,317.33
"East","06/02/2011",353.39,123.69
"East","06/02/2011",399.33,139.76
"East","06/02/2011",411.45,144.01
"East","06/03/2011",211.94,174.18
"East","06/03/2011",246.76,186.37
"East","06/04/2011",236.43,182.75
"East","06/04/2011",267.17,193.51
"East","06/05/2011",432.65,345.23
"East","06/05/2011",544.74,455.26
"East","06/05/2011",677.76,544.27
"East","06/06/2011",201.28,130.45
"East","06/06/2011",207.40,172.59
"East","06/07/2011",138.40,108.44
"East","06/07/2011",156.39,104.74
"East","06/07/2011",161.14,106.40
"West","06/01/2011",111.53,98.89
"West","06/01/2011",239.52,217.33
"West","06/02/2011",233.39,113.69
"West","06/02/2011",239.33,119.76
"West","06/02/2011",231.45,114.01
"West","06/03/2011",231.94,114.18
"West","06/03/2011",236.76,116.37
"West","06/04/2011",226.43,112.75
"West","06/04/2011",217.17,113.51
"West","06/05/2011",212.65,245.23
"West","06/05/2011",214.74,255.26
"West","06/05/2011",217.76,144.27
"West","06/06/2011",201.28,110.45
"West","06/06/2011",206.40,112.59
"West","06/07/2011",118.40,98.44
"West","06/07/2011",116.39,97.74
"West","06/07/2011",111.14,93.40
;
run;

proc sort data=sales;
by month year region;
run;

data pastmonths;
infile datalines dlm=',' dsd;
input date : mmddyy10. totsales totcost region $;
month = month(date);
year = year(date);
return;
datalines;
03/31/2011,1111.11,1212.12,"East"
04/30/2011,2222.22,2323.23,"East"
05/31/2011,3333.33,3434.34,"East"
03/31/2011,4444.44,4443.43,"West"
04/30/2011,5555.22,5321.23,"West"
05/31/2011,6666.33,6543.34,"West"
;
run;

proc sort data=pastmonths;
by month year region;
run;

data newdata(keep=xtra region date sales cost priorsales priorcost);
merge sales(in=mtd)
pastmonths(in=past);
by month year region;
xtra = catt(region,'XXX');
if mtd and past then do;
if first.region = 1 then do;
priorsales = totsales;
priorcost = totcost;
end;
else do;
priorsales = 0;
priorcost = 0;
end;
output;
end;
run;

ods listing;
proc print data=newdata;
title 'what does new version of data look like';
title2 'Note that prior month info is only on 1st obs for each region';
title3 'So summary using BREAK will be OK in PROC REPORT';
format date mmddyy10.;
run;

ods listing close;
ods html file='c:\temp\mult_break_lines_report4.html' style=sasweb;
proc report data=newdata nowd;
title '4) Use Merged Data Values for _RBREAK_ Line';
column region xtra date showdate priorsales priorcost sales cost;
define region / group 'Region';
define xtra / group noprint;
define date / group noprint;
define showdate / computed 'Date'
style(column)={just=r} ;
define priorsales /sum f=comma10.2 'Prior Sales' /* noprint */;
define priorcost / sum f=comma10.2 'Prior Cost' /* noprint */;
define sales /sum f=comma10.2 'Sales';
define cost / sum f=comma10.2 'Cost';

break after xtra / summarize;
break after region/ summarize;
compute showdate / character length=30;
showdate = put(date,mmddyy10.);
if upcase(_break_) = 'XTRA' then
showdate = 'M-T-D Totals';
else if upcase(_break_) = 'REGION' then
showdate = 'Prior Month Totals';
endcomp;
compute after region;
** use vars from pastmonths data to populate the summary line;
sales.sum = priorsales.sum;
cost.sum = priorcost.sum;
line ' ';
endcomp;
run;
ods _all_ close;
title; footnote;
[/pre]

Re: How to Add Additional Prior Month Total in PROC Report

c8826024 — Mon, 27 Jun 2011 02:07:19 GMT

Cynthia,

That is a really nice solution. The fake dataset you created is very similar to my dataset. I am not very familiar with COMPUTE and BREAK AFTER statement in PROC REPORT. I need to create different reports by different regions. So I use BY statement in PROC REPORT to produce different region report in different EXCEL worksheet. Then I try to twist your BREAK AFTER and COMPUTE AFTER statement. But it does give me the result I am looking for. Do you have any idea?

Thanks again.

Re: How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Mon, 27 Jun 2011 04:24:50 GMT

Hi:

As an alternative to BY group processing, you could remove the BY statement and use PAGE processing (which is unique to PROC REPORT and somewhat similar to the PAGE dimension in TABULATE). You would need to modify the BREAK statement:

break after region / summarize page;

(and, be sure to get rid of the BY statement, too)

ODS TAGSETS.EXCELXP (which is the destination that creates multiple worksheets automatically), will respect the PAGE that's created for every REGION and will then make a new worksheet for every region. You might also discover that Excel uses a default width for the SHOWDATE column and you might also need to modify or change the cellwidth in the DEFINE statement for SHOWDATE:

define showdate / computed 'Date'

style(column)={just=r cellwidth=1.5in};

But I think a combination of the above techniques (using TAGSETS.EXCELXP, using the PAGE option on the BREAK statement and possibly altering the DEFINE statement for SHOWDATE) should work to make 2 sheets (using my data) -- one for EAST and another for WEST.

cynthia

Re: How to Add Additional Prior Month Total in PROC Report

Ksharp — Mon, 27 Jun 2011 07:33:12 GMT

Maybe you need to process your dataset firstly.

datasales;

infile datalines dlm = ',' dsd;

input region $ date : ddmmyy10.sales cost;

month = month(date);

format date ddmmyy10.;

datalines;

"East","06/01/2011",142.53,114.89

"East","06/01/2011",349.52,317.33

"East","06/02/2011",353.39,123.69

"East","06/02/2011",399.33,139.76

"East","06/02/2011",411.45,144.01

"East","06/03/2011",211.94,174.18

"East","06/03/2011",246.76,186.37

"East","06/04/2011",236.43,182.75

"East","06/04/2011",267.17,193.51

"West","06/01/2011",142.53,114.89

"West","06/01/2011",349.52,317.33

"West","06/02/2011",353.39,123.69

"West","06/02/2011",399.33,139.76

"West","06/02/2011",411.45,144.01

"West","06/03/2011",211.94,174.18

"West","06/03/2011",246.76,186.37

"West","06/04/2011",236.43,182.75

"West","06/04/2011",267.17,193.51

;

run;

proc means data=sales nway noprint;

class region month;

var sales cost;

output out=want(drop=_:) sum=/autoname;

run;

datawant;

set want;

lag_sales_sum=lag(sales_sum);

lag_cost_sum=lag(cost_sum);

if month eq 1 then call missing(lag_sales_sum ,lag_cost_sum);

run;

datasales;

merge sales want;

by region month;

run;

ods html file='c:\temp\xx.html' style=sasweb;

proc report data=sales nowd out=test;

by region;

column month date sales cost sales_sumcost_sum lag_sales_sum lag_cost_sum;

define month/order noprint;

define date/display;

define sales_sum/display noprint;

define cost_sum/display noprint;

define lag_sales_sum/display noprint;

define lag_cost_sum/display noprint;

compute lag_cost_sum;

if missing(_break_) then do;

_sales_sum=sales_sum;_cost_sum=cost_sum;

_lag_sales_sum=lag_sales_sum; _lag_cost_sum=lag_cost_sum;

end;

endcomp;

compute after month;

line 'M-t-d' @10 _sales_sum best8. @20 _cost_sum best8.;

line 'Pri-m-t' @10 _lag_sales_sum best8. @20 _lag_cost_sum best8.;

endcomp;

run;

ods html close;

Ksharp

How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Mon, 27 Jun 2011 14:04:35 GMT

Hi:

While a DATA step pre-processing approach is one possible approach, I don't believe it is necessary. With just 2 changes to my previously posted code, I was able to generate a worksheet for each region using TAGSETS.EXCELXP. The other bump in your approach (to write the extra summary lines with LINE statements) is that there is a high chance that LINE statement output will not appear correctly because the @10 and @20 really only appear as you intend in the LISTING destination (output window). The ODS HTML approach will result in both regions appearing on one sheet when the file is opened in Excel. Also, you would have to use either ODS HTML3 or ODS MSOFFICE2K in order to have the STYLE=SASWEB respected, since Excel isn't really happy about using the style with ODS HTML 4.0 created HTML tags.

cynthia

How to Add Additional Prior Month Total in PROC Report

Ksharp — Tue, 28 Jun 2011 05:07:04 GMT

Hi. Cynthia.

I know it is not necessary to process data firstly. But For op's purpose , Using only one proc report is more difficult than data step.So I prefer to pre-process data for some situation proc report faced difficultly. For @10

it is depend with different destination.Since op did not mention what his destination,and he or she want to use by statement in proc report, so i just want to achieve it by op's purpose.

Ksharp

How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Tue, 28 Jun 2011 06:52:19 GMT

Hi,

As I said, pre-processing is a viable alternative.

However, the PAGE approach works just as well as BY group processing -- for the purpose of creating multiple sheets. And, since the OP said the main interest was in multi-sheet workbooks in Excel -- this, to me, pointed to TAGSETS.EXCELXP as the destination -- one of the destinations where the LINE statement with @column positioning would not be respected.

cynthia

How to Add Additional Prior Month Total in PROC Report

c8826024 — Tue, 28 Jun 2011 20:01:48 GMT

Thanks Cynthia.

I use SHEET_INTERVAL='bygroup' options in EXCELXP TAGSET to populate the tables name by region. If I use your solution, it seems that I cannot do it anymore. Do you have other alternative way?

Re: How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Wed, 29 Jun 2011 03:12:52 GMT

Hi:

The default behavior of ODS TAGSETS.EXCELXP is for sheet_interval to be set to 'TABLE' -- when PROC REPORT makes a new table for every page, this default sheet_interval value will make a new sheet for every region's "page" -- without needing to reset the option. So, essentially, you do not need to use sheet_interval='bygroup' -- especially if you take OUT the BY statement, there would be no BY group for sheet_interval='bygroup' to interface with.

The code at the bottom of the post is what I used (only the TAGSETS.EXCELXP section is shown)...the rest of the processing and data is the same as previously posted.

cynthia

ods listing close;
ods tagsets.excelxp file='c:\temp\use_break_page5.xls' style=sasweb;
proc report data=newdata nowd;
title '5) Use Merged Data Values for _RBREAK_ Line';
column region xtra date showdate priorsales priorcost sales cost;
define region / group 'Region';
define xtra / group noprint;
define date / group noprint;
define showdate / computed 'Date'
         style(column)={just=r cellwidth=1.5in} ;
define priorsales /sum f=comma10.2 'Prior Sales' /* noprint */;
define priorcost / sum f=comma10.2 'Prior Cost' /* noprint */;
define sales /sum f=comma10.2 'Sales';
define cost / sum f=comma10.2 'Cost';

break after xtra / summarize;
break after region/ summarize page;
compute showdate / character length=30;
     showdate = put(date,mmddyy10.);
     if upcase(_break_) = 'XTRA' then
        showdate = 'M-T-D Totals';
     else if upcase(_break_) = 'REGION' then
        showdate = 'Prior Month Totals';
endcomp;
compute after region;
     ** use vars from pastmonths data to populate the summary line;
     sales.sum = priorsales.sum;
     cost.sum = priorcost.sum;
     line ' ';
endcomp;
run;
ods _all_ close;
title; footnote;

Re: How to Add Additional Prior Month Total in PROC Report

Ksharp — Wed, 29 Jun 2011 05:29:49 GMT

There are one more way to use line statement.

As I said, you need to adjust it with the different destionation.

data sales;
  infile datalines dlm = ',' dsd;
  input region $ date : ddmmyy10. sales cost;
  month = month(date);
  format date ddmmyy10.;
datalines;
"East","06/01/2011",142.53,114.89
"East","06/01/2011",349.52,317.33
"East","06/02/2011",353.39,123.69
"East","06/02/2011",399.33,139.76
"East","06/02/2011",411.45,144.01
"East","06/03/2011",211.94,174.18
"East","06/03/2011",246.76,186.37
"East","06/04/2011",236.43,182.75
"East","06/04/2011",267.17,193.51
"West","06/01/2011",142.53,114.89
"West","06/01/2011",349.52,317.33
"West","06/02/2011",353.39,123.69
"West","06/02/2011",399.33,139.76
"West","06/02/2011",411.45,144.01
"West","06/03/2011",211.94,174.18
"West","06/03/2011",246.76,186.37
"West","06/04/2011",236.43,182.75
"West","06/04/2011",267.17,193.51
;
run;

proc means data=sales nway noprint;
class region month;
var sales cost;
output out=want(drop=_:) sum=/autoname;
run;

data want;
set want;
lag_sales_sum=lag(sales_sum);
lag_cost_sum=lag(cost_sum);
if month eq 1 then call missing(lag_sales_sum ,lag_cost_sum);
run;

data sales;
merge sales want;
by region month;
run;

ods tagsets.excelxp file='c:\temp\use_break_page5.xls' 
  options(SHEET_INTERVAL='bygroup')  style=sasweb;

proc report data=sales nowd;
by region;
column month date sales cost sales_sum cost_sum lag_sales_sum lag_cost_sum;
define month/order noprint;
define date/display;
define sales_sum/display noprint;
define cost_sum/display noprint;
define lag_sales_sum/display noprint;
define lag_cost_sum/display noprint;
compute lag_cost_sum;
if missing(_break_) then do;
_sales_sum=sales_sum;_cost_sum=cost_sum;
_lag_sales_sum=lag_sales_sum; _lag_cost_sum=lag_cost_sum;
end;
endcomp;
compute after month;
  str_sum='          '||_sales_sum||'  '||_cost_sum;
  str_lag_sum='      '||_lag_sales_sum||'    '||_lag_cost_sum;
  line @1 'M-t-d:' str_sum $50.;
  line @1 'Pri-m-t:' str_lag_sum $50.;
  line ' ';
  line ' ';
endcomp;

run;

ods tagsets.excelxp close;

OK. I understand what your mean. Maybe your method is a better choice.

Ksharp

Re: How to Add Additional Prior Month Total in PROC Report

c8826024 — Wed, 29 Jun 2011 17:12:40 GMT

Thank you so much. I will try both solution.

Re: How to Add Additional Prior Month Total in PROC Report

c8826024 — Thu, 09 Feb 2012 14:53:41 GMT

Ok, my manager want to change the report template AGAIN. He want to have prior month totle across different region and all region report should be in different worksheet BUT one workbook.

Here is my source data:

proc_mm	country	process_dt	a1	a2	a7	a9	a11	a_total	b1	b2	b7	b9	b11	b_total	ab_total	realcoamt
1/1/2012	1.NA	1/1/2012	100	200	300	400	1000	100	100	1100	1000
1/1/2012	1.NA	1/2/2012	100	200	300	400	1000	101	101	1101	1000
1/1/2012	1.NA	1/3/2012	100	200	300	400	1000	102	102	1102	1000
1/1/2012	1.NA	1/4/2012	100	200	300	400	1000	103	103	1103	1000
1/1/2012	1.NA	1/5/2012	100	200	300	400	1000	104	104	1104	1000
1/1/2012	1.NA	1/6/2012	100	200	300	400	1000	105	105	1105	1000
1/1/2012	1.NA	1/7/2012	100	200	300	400	1000	106	106	1106	1000
1/1/2012	1.NA	1/8/2012	100	200	300	400	1000	107	107	1107	1000
1/1/2012	1.NA	1/9/2012	100	200	300	400	1000	108	108	1108	1000
1/1/2012	1.NA	1/10/2012	100	200	300	400	1000	109	109	1109	1000
1/1/2012	1.NA	1/11/2012	100	200	300	400	1000	110	110	1110	1000
1/1/2012	1.NA	1/12/2012	100	200	300	400	1000	111	111	1111	1000
1/1/2012	1.NA	1/13/2012	100	200	300	400	1000	112	112	1112	1000
1/1/2012	1.NA	1/14/2012	100	200	300	400	1000	113	113	1113	1000
1/1/2012	1.NA	1/15/2012	100	200	300	400	1000	114	114	1114	1000
1/1/2012	1.NA	1/16/2012	100	200	300	400	1000	115	115	1115	1000
1/1/2012	1.NA	1/17/2012	100	200	300	400	1000	116	116	1116	1000
1/1/2012	1.NA	1/18/2012	100	200	300	400	1000	117	117	1117	1000
1/1/2012	1.NA	1/19/2012	100	200	300	400	1000	118	118	1118	1000
1/1/2012	1.NA	1/20/2012	100	200	300	400	1000	119	119	1119	1000
1/1/2012	1.NA	1/21/2012	100	200	300	400	1000	120	120	1120	1000
1/1/2012	1.NA	1/22/2012	100	200	300	400	1000	121	121	1121	1000
1/1/2012	1.NA	1/23/2012	100	200	300	400	1000	122	122	1122	1000
1/1/2012	1.NA	1/24/2012	100	200	300	400	1000	123	123	1123	1000
1/1/2012	1.NA	1/25/2012	100	200	300	400	1000	124	124	1124	1000
1/1/2012	1.NA	1/26/2012	100	200	300	400	1000	125	125	1125	1000
1/1/2012	1.NA	1/27/2012	100	200	300	400	1000	126	126	1126	1000
1/1/2012	1.NA	1/28/2012	100	200	300	400	1000	127	127	1127	1000
1/1/2012	1.NA	1/29/2012	100	200	300	400	1000	128	128	1128	1000
1/1/2012	1.NA	1/30/2012	100	200	300	400	1000	129	129	1129	1000
1/1/2012	1.NA	1/31/2012	100	200	300	400	1000	130	130	1130	1000
1/1/2012	2.UK	1/1/2012	100	200	300	400	1000	131	131	1131	1000
1/1/2012	2.UK	1/2/2012	100	200	300	400	1000	132	132	1132	1000
1/1/2012	2.UK	1/3/2012	100	200	300	400	1000	133	133	1133	1000
1/1/2012	2.UK	1/4/2012	100	200	300	400	1000	134	134	1134	1000
1/1/2012	2.UK	1/5/2012	100	200	300	400	1000	135	135	1135	1000
1/1/2012	2.UK	1/6/2012	100	200	300	400	1000	136	136	1136	1000
1/1/2012	2.UK	1/7/2012	100	200	300	400	1000	137	137	1137	1000
1/1/2012	2.UK	1/8/2012	100	200	300	400	1000	138	138	1138	1000
1/1/2012	2.UK	1/9/2012	100	200	300	400	1000	139	139	1139	1000
1/1/2012	2.UK	1/10/2012	100	200	300	400	1000	140	140	1140	1000
1/1/2012	2.UK	1/11/2012	100	200	300	400	1000	141	141	1141	1000
1/1/2012	2.UK	1/12/2012	100	200	300	400	1000	142	142	1142	1000
1/1/2012	2.UK	1/13/2012	100	200	300	400	1000	143	143	1143	1000
1/1/2012	2.UK	1/14/2012	100	200	300	400	1000	144	144	1144	1000
1/1/2012	2.UK	1/15/2012	100	200	300	400	1000	145	145	1145	1000
1/1/2012	2.UK	1/16/2012	100	200	300	400	1000	146	146	1146	1000
1/1/2012	2.UK	1/17/2012	100	200	300	400	1000	147	147	1147	1000
1/1/2012	2.UK	1/18/2012	100	200	300	400	1000	148	148	1148	1000
1/1/2012	2.UK	1/19/2012	100	200	300	400	1000	149	149	1149	1000
1/1/2012	2.UK	1/20/2012	100	200	300	400	1000	150	150	1150	1000
1/1/2012	2.UK	1/21/2012	100	200	300	400	1000	151	151	1151	1000
1/1/2012	2.UK	1/22/2012	100	200	300	400	1000	152	152	1152	1000
1/1/2012	2.UK	1/23/2012	100	200	300	400	1000	153	153	1153	1000
1/1/2012	2.UK	1/24/2012	100	200	300	400	1000	154	154	1154	1000
1/1/2012	2.UK	1/25/2012	100	200	300	400	1000	155	155	1155	1000
1/1/2012	2.UK	1/26/2012	100	200	300	400	1000	156	156	1156	1000
1/1/2012	2.UK	1/27/2012	100	200	300	400	1000	157	157	1157	1000
1/1/2012	2.UK	1/28/2012	100	200	300	400	1000	158	158	1158	1000
1/1/2012	2.UK	1/29/2012	100	200	300	400	1000	159	159	1159	1000
1/1/2012	2.UK	1/30/2012	100	200	300	400	1000	160	160	1160	1000
1/1/2012	2.UK	1/31/2012	100	200	300	400	1000	161	161	1161	1000
1/1/2012	3.DE	1/1/2012	100	200	300	400	1000	162	162	1162	1000
1/1/2012	3.DE	1/2/2012	100	200	300	400	1000	163	163	1163	1000
1/1/2012	3.DE	1/3/2012	100	200	300	400	1000	164	164	1164	1000
1/1/2012	3.DE	1/4/2012	100	200	300	400	1000	165	165	1165	1000
1/1/2012	3.DE	1/5/2012	100	200	300	400	1000	166	166	1166	1000
1/1/2012	3.DE	1/6/2012	100	200	300	400	1000	167	167	1167	1000
1/1/2012	3.DE	1/7/2012	100	200	300	400	1000	168	168	1168	1000
1/1/2012	3.DE	1/8/2012	100	200	300	400	1000	169	169	1169	1000
1/1/2012	3.DE	1/9/2012	100	200	300	400	1000	170	170	1170	1000
1/1/2012	3.DE	1/10/2012	100	200	300	400	1000	171	171	1171	1000
1/1/2012	3.DE	1/11/2012	100	200	300	400	1000	172	172	1172	1000
1/1/2012	3.DE	1/12/2012	100	200	300	400	1000	173	173	1173	1000
1/1/2012	3.DE	1/13/2012	100	200	300	400	1000	174	174	1174	1000
1/1/2012	3.DE	1/14/2012	100	200	300	400	1000	175	175	1175	1000
1/1/2012	3.DE	1/15/2012	100	200	300	400	1000	176	176	1176	1000
1/1/2012	3.DE	1/16/2012	100	200	300	400	1000	177	177	1177	1000
1/1/2012	3.DE	1/17/2012	100	200	300	400	1000	178	178	1178	1000
1/1/2012	3.DE	1/18/2012	100	200	300	400	1000	179	179	1179	1000
1/1/2012	3.DE	1/19/2012	100	200	300	400	1000	180	180	1180	1000
1/1/2012	3.DE	1/20/2012	100	200	300	400	1000	181	181	1181	1000
1/1/2012	3.DE	1/21/2012	100	200	300	400	1000	182	182	1182	1000
1/1/2012	3.DE	1/22/2012	100	200	300	400	1000	183	183	1183	1000
1/1/2012	3.DE	1/23/2012	100	200	300	400	1000	184	184	1184	1000
1/1/2012	3.DE	1/24/2012	100	200	300	400	1000	185	185	1185	1000
1/1/2012	3.DE	1/25/2012	100	200	300	400	1000	186	186	1186	1000
1/1/2012	3.DE	1/26/2012	100	200	300	400	1000	187	187	1187	1000
1/1/2012	3.DE	1/27/2012	100	200	300	400	1000	188	188	1188	1000
1/1/2012	3.DE	1/28/2012	100	200	300	400	1000	189	189	1189	1000
1/1/2012	3.DE	1/29/2012	100	200	300	400	1000	190	190	1190	1000
1/1/2012	3.DE	1/30/2012	100	200	300	400	1000	191	191	1191	1000
1/1/2012	3.DE	1/31/2012	100	200	300	400	1000	192	192	1192	1000
1/1/2012	8.Global Total	1/1/2012	300	600	900	0	1200	3000	0	0	394	0	0	394	3394	1000
1/1/2012	8.Global Total	1/2/2012	300	600	900	0	1200	3000	0	0	397	0	0	397	3397	1000
1/1/2012	8.Global Total	1/3/2012	300	600	900	0	1200	3000	0	0	400	0	0	400	3400	1000
1/1/2012	8.Global Total	1/4/2012	300	600	900	0	1200	3000	0	0	403	0	0	403	3403	1000
1/1/2012	8.Global Total	1/5/2012	300	600	900	0	1200	3000	0	0	406	0	0	406	3406	1000
1/1/2012	8.Global Total	1/6/2012	300	600	900	0	1200	3000	0	0	409	0	0	409	3409	1000
1/1/2012	8.Global Total	1/7/2012	300	600	900	0	1200	3000	0	0	412	0	0	412	3412	1000
1/1/2012	8.Global Total	1/8/2012	300	600	900	0	1200	3000	0	0	415	0	0	415	3415	1000
1/1/2012	8.Global Total	1/9/2012	300	600	900	0	1200	3000	0	0	418	0	0	418	3418	1000
1/1/2012	8.Global Total	1/10/2012	300	600	900	0	1200	3000	0	0	421	0	0	421	3421	1000
1/1/2012	8.Global Total	1/11/2012	300	600	900	0	1200	3000	0	0	424	0	0	424	3424	1000
1/1/2012	8.Global Total	1/12/2012	300	600	900	0	1200	3000	0	0	427	0	0	427	3427	1000
1/1/2012	8.Global Total	1/13/2012	300	600	900	0	1200	3000	0	0	430	0	0	430	3430	1000
1/1/2012	8.Global Total	1/14/2012	300	600	900	0	1200	3000	0	0	433	0	0	433	3433	1000
1/1/2012	8.Global Total	1/15/2012	300	600	900	0	1200	3000	0	0	436	0	0	436	3436	1000
1/1/2012	8.Global Total	1/16/2012	300	600	900	0	1200	3000	0	0	439	0	0	439	3439	1000
1/1/2012	8.Global Total	1/17/2012	300	600	900	0	1200	3000	0	0	442	0	0	442	3442	1000
1/1/2012	8.Global Total	1/18/2012	300	600	900	0	1200	3000	0	0	445	0	0	445	3445	1000
1/1/2012	8.Global Total	1/19/2012	300	600	900	0	1200	3000	0	0	448	0	0	448	3448	1000
1/1/2012	8.Global Total	1/20/2012	300	600	900	0	1200	3000	0	0	451	0	0	451	3451	1000
1/1/2012	8.Global Total	1/21/2012	300	600	900	0	1200	3000	0	0	454	0	0	454	3454	1000
1/1/2012	8.Global Total	1/22/2012	300	600	900	0	1200	3000	0	0	457	0	0	457	3457	1000
1/1/2012	8.Global Total	1/23/2012	300	600	900	0	1200	3000	0	0	460	0	0	460	3460	1000
1/1/2012	8.Global Total	1/24/2012	300	600	900	0	1200	3000	0	0	463	0	0	463	3463	1000
1/1/2012	8.Global Total	1/25/2012	300	600	900	0	1200	3000	0	0	466	0	0	466	3466	1000
1/1/2012	8.Global Total	1/26/2012	300	600	900	0	1200	3000	0	0	469	0	0	469	3469	1000
1/1/2012	8.Global Total	1/27/2012	300	600	900	0	1200	3000	0	0	472	0	0	472	3472	1000
1/1/2012	8.Global Total	1/28/2012	300	600	900	0	1200	3000	0	0	475	0	0	475	3475	1000
1/1/2012	8.Global Total	1/29/2012	300	600	900	0	1200	3000	0	0	478	0	0	478	3478	1000
1/1/2012	8.Global Total	1/30/2012	300	600	900	0	1200	3000	0	0	481	0	0	481	3481	1000
1/1/2012	8.Global Total	1/31/2012	300	600	900	0	1200	3000	0	0	484	0	0	484	3484	1000
2/1/2012	1.NA	2/1/2012	100	200	300	400	1000	224	224	1224	1000
2/1/2012	1.NA	2/2/2012	100	200	300	400	1000	225	225	1225	1000
2/1/2012	1.NA	2/3/2012	100	200	300	400	1000	226	226	1226	1000
2/1/2012	1.NA	2/4/2012	100	200	300	400	1000	227	227	1227	1000
2/1/2012	1.NA	2/5/2012	100	200	300	400	1000	228	228	1228	1000
2/1/2012	1.NA	2/6/2012	100	200	300	400	1000	229	229	1229	1000
2/1/2012	1.NA	2/7/2012	100	200	300	400	1000	230	230	1230	1000
2/1/2012	2.UK	2/1/2012	100	200	300	400	1000	231	231	1231	1000
2/1/2012	2.UK	2/2/2012	100	200	300	400	1000	232	232	1232	1000
2/1/2012	2.UK	2/3/2012	100	200	300	400	1000	233	233	1233	1000
2/1/2012	2.UK	2/4/2012	100	200	300	400	1000	234	234	1234	1000
2/1/2012	2.UK	2/5/2012	100	200	300	400	1000	235	235	1235	1000
2/1/2012	2.UK	2/6/2012	100	200	300	400	1000	236	236	1236	1000
2/1/2012	2.UK	2/7/2012	100	200	300	400	1000	237	237	1237	1000
2/1/2012	3.DE	2/1/2012	100	200	300	400	1000	238	238	1238	1000
2/1/2012	3.DE	2/2/2012	100	200	300	400	1000	239	239	1239	1000
2/1/2012	3.DE	2/3/2012	100	200	300	400	1000	240	240	1240	1000
2/1/2012	3.DE	2/4/2012	100	200	300	400	1000	241	241	1241	1000
2/1/2012	3.DE	2/5/2012	100	200	300	400	1000	242	242	1242	1000
2/1/2012	3.DE	2/6/2012	100	200	300	400	1000	243	243	1243	1000
2/1/2012	3.DE	2/7/2012	100	200	300	400	1000	244	244	1244	1000
2/1/2012	8.Global Total	2/1/2012	300	600	900	0	1800	3000	0	0	693	0	0	693	3693	3000
2/1/2012	8.Global Total	2/2/2012	300	600	900	0	1800	3000	0	0	696	0	0	696	3696	3000
2/1/2012	8.Global Total	2/3/2012	300	600	900	0	1800	3000	0	0	699	0	0	699	3699	3000
2/1/2012	8.Global Total	2/4/2012	300	600	900	0	1800	3000	0	0	702	0	0	702	3702	3000
2/1/2012	8.Global Total	2/5/2012	300	600	900	0	1800	3000	0	0	705	0	0	705	3705	3000
2/1/2012	8.Global Total	2/6/2012	300	600	900	0	1800	3000	0	0	708	0	0	708	3708	3000
2/1/2012	8.Global Total	2/7/2012	300	600	900	0	1800	3000	0	0	711	0	0	711	3711	3000

The code provided seems not working. Any idea?

How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Thu, 09 Feb 2012 17:21:15 GMT

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

How to Add Additional Prior Month Total in PROC Report

c8826024 — Thu, 09 Feb 2012 18:03:21 GMT

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;

How to Add Additional Prior Month Total in PROC Report

c8826024 — Thu, 09 Feb 2012 18:05:52 GMT

Here is the template I want:

Pre Charge-Off

Post Charge-Off

Process Date

a11

a_total

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

How to Add Additional Prior Month Total in PROC Report

c8826024 — Thu, 09 Feb 2012 18:07:40 GMT

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

How to Add Additional Prior Month Total in PROC Report

Cynthia_sas — Thu, 09 Feb 2012 19:16:20 GMT

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