BookmarkSubscribeRSS Feed
aravind
Fluorite | Level 6
Hi there,

I am trying to create a report in pdf format.
I have the following dataset
region county
1 old
1 new
1 new
2 modern
3 modern

And I am trying to get the following report with a page break by county.

the first page of the report should look like the following
region county
1 old
county total : 1

the second page of the report should look like the following
region county
1 new
1 new
county total : 2
region total : 3

the third page of the report should look like the following
region county
2 modern
2 modern
county total : 2
region total : 2
grand total : 5

********** END OF REPORT ********************

I am trying to use the proc report and i got the retion and county fileds but couldn't get the totals.
Help will be greatly appreciated.

Thanks
Aravind
10 REPLIES 10
Cynthia_sas
SAS Super FREQ
Hi:
Can you post your PROC REPORT code??? Are you using a BREAK statement and/or an RBREAK statement???

Also, your original data shows region 2 and region 3 for modern:
[pre]
2 modern
3 modern
[/pre]

But your desired output shows only region 2 for modern:
[pre]
2 modern
2 modern
[/pre]

So which data values are correct??? What you show as the input data or what you show in the desired report output??

Also, it seems odd that you only have 2 items on your report -- do you have other numeric variables that you are not showing or that you need to summarize? It seems that you want to insert a break after every unique REGION/COUNTY combination, but rather than just a summary report, such as you might get from PROC FREQ or PROC MEANS, it looks like you want to list ALL the detail rows for the data and the count of the rows. Is that correct???

You may or may not know that PROC REPORT needs report items to be group or order items in order to perform PAGE processing (such as you want). The side effect of an item being defined as a group or order item with PROC REPORT is that repetitious values are suppressed for each group. So this might be problematic for the rows where the region and county are exactly the same, especially if there are no other report items on your report row.

cynthia
aravind
Fluorite | Level 6
sorry about the data error.

the orginial data is
region county
1 100
1 200
1 200
2 300
2 300

I only got just the region and county fileds in my proc report with a page break by county but i did't get any idea how to print the totals.
Cynthia_sas
SAS Super FREQ
Hi:
So now, I'm confused, what happened to "old" and "modern"?? What do the numbers for COUNTY represent??? Should the values for COUNTY be added up or are they merely identifiers???

Since your data is stil not clear to me, consider the program below that uses a subset of SASHELP.SHOES. The program produces 3 reports:
1) a detail report -- where the main report items are all defined with a usage of DISPLAY and then the report shows the total count of observations for the 2 regions on the report and
2) a detail report -- where the main report items are all defined with a usage of ORDER and there is a summary line between groups and at the end of the report and
3) a summary report -- where the observations are "collapsed" by region and product because they are defined as GROUP usage and the SUBSIDIARY variable has been removed from the report to show the impact on the N statistic and the totals.

The various BREAK and RBREAK statements are writing the summary lines to the report. The usage of DISPLAY or ORDER or GROUP is what determines whether a report is a "detail" report or a "summary" report. With a usage of DISPLAY, the only total that is available to you is the "grand total" or the report total. Once you move into the world of ORDER or GROUP usage, then you can put subtotals or group totals on the report. If you don't know how PROC REPORT operates, it will be useful for you to carefully compare the differences between the 3 reports so that you understand what I meant when I said that PROC REPORT will suppress the repetitious display of GROUP or ORDER values on the report rows.

It seems too early to get into a discussion of the PAGE option for the BREAK statement yet until you are comfortable with the basics of PROC REPORT and report DEFINE statement usage and how usage works with break processing and whether a report is a detail report or a summary report.

As an alternate example, consider the PROC FREQ report produced as a second step in my program. It produces output that may be of some use to you.

cynthia
[pre]
ods listing close;
options nodate nonumber;

proc sort data=sashelp.shoes out=shoes;
by region product;
where region in ('Asia', 'Canada') and
product in ('Boot', 'Slipper');
run;

ods pdf file='c:\temp\show_diff_shoexxx.pdf';
proc report data=shoes nowd;
title '1) All Items DISPLAY with report total';
title2 'Note how COUNT is 1 for every row';
column region product subsidiary n;
define region / display;
define product / display;
define subsidiary / display;
define n / 'Row Count';
rbreak after / summarize;
compute after;
region = 'All Regions';
endcomp;
run;

proc report data=shoes nowd;
title '2) Use ORDER for REGION, PRODUCT and SUBSIDIARY items with report total';
column region product subsidiary n;
define region / ORDER;
define product / ORDER;
define subsidiary / ORDER;
define n / 'Subsidiary Row Count';
rbreak after / summarize;
break after region / summarize;
compute after region;
line ' ';
endcomp;
compute after;
region = 'All Regions';
endcomp;
run;

proc report data=shoes nowd;
title '3) Use GROUP for REGION and PRODUCT items';
title2 'To show summary or "collapse" of rows, subsidiary has been removed from the report';
column region product n;
define region / GROUP;
define product / GROUP;
define n / 'Subsidiary Row Count';
rbreak after / summarize;
break after region / summarize;
compute after region;
line ' ';
endcomp;
compute after;
region = 'All Regions';
endcomp;
run;
ods pdf close;
[/pre]

ods pdf file='c:\temp\show_freq.pdf';
title 'Show PROC FREQ with LIST option';

proc freq data=shoes nlevels;
tables region*product / list;
run;
ods pdf close;
Ksharp
Super User
Hi.
I only get part successfully.
Maybe you can get better base on it.Good Luck.

[pre]
data temp;
input region $ county $ ;
datalines;
1 100
1 200
1 200
2 300
2 300
;
run;
ods pdf file='c:\total.pdf' style=sasweb;
proc report data=temp nowd out=test;
column region county ;
define region /order ;
define county /order ;

compute region;
if _break_ eq ' ' then do;
total_county+1;
total_region+1;
total_grand+1;
end;
else if _break_ eq 'county' then do;
t_county=total_county;
total_county=0;
end;
else if _break_ eq 'region' then total_region=0;
endcomp;

compute after county;
line 'county total:' t_county 3.;

line 'region total:' total_region 3.;

line 'grand total:' total_grand 3.;
endcomp;

break after region /suppress;
break after county /page ;

run;
ods pdf close;
[/pre]


Ksharp
Cynthia_sas
SAS Super FREQ
Also, in this paper:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

on pages 7 and 8, it shows how to write "extra" lines at the break without using the LINE statement.

cynthia
Ksharp
Super User
Hi. Cynthia.
I know that skill.
But the 'line' statement is more powerful and flexibility.So prefer to use 'line'.
And I also think useing only one proc report is hard to achieve this target,so using data step to pre-process origin data ,it will be better.
Be honest,it seems like a piece of cake, but it really waste my lots of time, which remind me never to neglect the little easy thing, maybe it is a big and tough problem.
Finally I complete it and get result what op want.

[pre]
data temp;
input region $ county $ ;
datalines;
1 100
1 200
1 200
2 300
2 300
;
run;
proc sort data=temp;
by region county;
run;
data temp;
set temp end=last;
by region;
_region=region;
_county=county;

if county ne lag(county) then do; break+1; total_county=0; end;
if region ne lag(region) then total_region=0;
if last.region then region_len=20;
else region_len=0;
if last then grand_len=20;
else grand_len=0;

total_county+1; str_county=cats('county total: ',total_county);
total_region+1; str_region=cats('region total: ',total_region);
total_grand+1; str_grand=cats('grand total: ',total_grand);
run;

ods pdf file='c:\total.pdf' style=sasweb;
proc report data=temp nowd out=test;
column region county _region _county break region_len grand_len str_county str_region str_grand;
define region /order width=10 noprint;
define county /order width=10 noprint;
define _region/display;
define _county /display;
define break / order noprint;
define region_len/ noprint;
define grand_len/ noprint;
define str_county/ noprint;
define str_region/noprint;
define str_grand/noprint;

compute str_grand;
if _break_ eq ' ' then do;
_str_county=str_county;
_str_region=str_region;
_str_grand=str_grand;
end;
endcomp;

compute after break;

line _str_county $20.;
line _str_region $varying. region_len.sum;
line _str_grand $varying. grand_len.sum;
endcomp;

break after break /page;
run;
ods pdf close;

[/pre]



Ksharp
Cynthia_sas
SAS Super FREQ
Hi:
Yes, I agree, the LINE statement is great. However, if this program was going to be turned into a stored process on the BI platform, then the WRS method of displaying stored process output does not recognize the PROC REPORT LINE statement output and you would have to use a technique such as that shown in the paper I referenced.

I also agree that sometimes it is easier to use PROC REPORT if you "pre-process" the data. That was the conclusion in my "Complex Reports" paper -- of the 8 programs that I discussed in that paper, I preprocessed the data in 6 of the programs using DATA step. But you have to first understand how PROC REPORT operates, by default, before you decide to just jump in and pre-process the data.

cynthia
aravind
Fluorite | Level 6
Thanks Cynthia,

For your time and explanation.
Really appreciate it.


Thanks & Regards
Aravind
aravind
Fluorite | Level 6
Hi Ksharp,

I really appreciate your solution.
I've got the solution in data _null_ but i had trouble in getting in proc report and your solution worked great.

Once again thanks for you time.

Here is the data _null_ code.
data test;
input region county;
cards;
1 100
1 200
1 200
2 300
2 300
;
run;

proc sort data=test out=test1;
by region county;
run;

data counts;
set test;
by region county;
if first.county then countytotal=0;
countytotal+1;
if first.region then regiontotal=0;
regiontotal+1;
if last.region then flag=regiontotal;
else flag =0;
grandtotal+flag;
drop flag;
run;

ods listing;

data _null_;
set counts end=eof;
file print;
by region county;
if first.county then put _page_;
put @1 region 1. @6 county 3.;
if last.county then put "County TOtal" countytotal comma6.;
if last.region then put "Region Total" regiontotal comma6.;
if eof then put "Grand Total" grandtotal comma6.;
run;

Thanks & Regards
Aravind.
Cynthia_sas
SAS Super FREQ
Hi:
Along the lines of 6 different ways to do things, here's a DATA step and PROC REPORT that does a bit more work in PROC REPORT and does NOT use the LINE statement and does NOT use the technique outlined in the paper.

For something completely different...

cynthia
[pre]
data temp;
length region county $15;
input region $ county $ ;
datalines;
1 100
1 200
1 200
2 300
2 300
;
run;

proc sort data=temp out=temp;
by region county;
run;

data pp_temp;
set temp;
by region county;
retain pg 0;
if first.county then pg + 1;

** as long as we are in a data step, make some display versions of region and county;
p_reg = region;
p_cnt = county;
run;

ods listing;
proc print data=pp_temp;
title 'What does pre-processed data look like??';
var region pg county p_reg p_cnt;
run;

ods listing close;
ods pdf file='c:\temp\output\alt_method.pdf';
proc report data=pp_temp nowd;
title 'Final Report';
column region pg county p_reg p_cnt n;
define pg / order noprint;
define region / order noprint;
define county / order noprint;
define p_reg / display 'Region'
style(column)={just=r};
define p_cnt / display 'County'
style(column)={just=r};
define n / 'count' noprint;
break before pg / page;
break after region / summarize;
break after pg / summarize;
rbreak after / summarize;
** Now put the count and the labels for the summary lines;
** into P_REG and P_CNT columns;
compute after pg;
p_reg = 'County Total:';
p_cnt = put(n,3.0);
endcomp;
compute after region;
p_reg = 'Region Total:';
p_cnt = put(n,3.0);
endcomp;
compute after;
p_reg = 'Grand Total:';
p_cnt = put(n,3.0);
endcomp;
run;
ods pdf close;
[/pre]

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
  • 10 replies
  • 2438 views
  • 0 likes
  • 3 in conversation