Desktop productivity for business analysts and programmers

Repeating Lines in Output for either Proc Tabulate/Proc Report

Reply
N/A
Posts: 0

Repeating Lines in Output for either Proc Tabulate/Proc Report

Hi,

Would like to know how to customize my report in proc Tabulate/proc report or any other ways to look as report1;

Report1 - show repeating lines at each rows
column1_____column2_____column3_____column4
Rene________NY_________Fast_________$30.00
Rene________NY_________Slow_________$50.00
Rene________CA_________Mod_________$30.00
Total_________________________________$110.00

Instead of report2 which is generated from proc report/tabulate;

Report2
column1_____column2_____column3_____column4
Rene________NY_________Fast_________$30.00
________________________Slow_________$50.00
____________CA_________Mod_________$30.00
Total_________________________________$110.00

Thanks a Ton!
Super Contributor
Super Contributor
Posts: 3,174

Re: Repeating Lines in Output for either Proc Tabulate/Proc Report

Suggest sharing your existing PROC REPORT code for most accurate feedback. Have a look at the DEFINE statement and the variable type defined in that statement (GROUP, DISPLAY).

Scott Barry
SBBWorks, Inc.

http://www2.sas.com/proceedings/sugi25/25/btu/25p067.pdf
SAS Super FREQ
Posts: 8,719

Re: Repeating Lines in Output for either Proc Tabulate/Proc Report

Hi:
What you want to do (repeat row header information) is easier with PROC REPORT than with PROC TABULATE. But, for both REPORT or TABULATE, you could make an output dataset of the summarized report -- the output dataset does not have duplicate row headers blanked out -- so then your final step would be to print the summarized dataset either with PROC PRINT or PROC REPORT.

OR, you could use PROC REPORT and a COMPUTED item on the report. You would need to learn about COMPUTE blocks and COMPUTED items and research in the documentation to use this method, as Scott suggested.

The output dataset method is shown below.
cynthia

[pre]
options missing = ' ';
proc report data=sashelp.shoes nowd out=work.repout;
where region in ('Asia', 'Canada') and
product in ('Slipper', 'Sandal');
title 'Before -- Report';
column region product sales ;
define region / group;
define product / group;
define sales / sum 'Sales';
run;

proc report data=work.repout nowd;
title 'After Proc Report--using OUT= dataset';
column region product sales;
rbreak after / summarize;
run;

proc tabulate data=sashelp.shoes out=work.tabout;
where region in ('Asia', 'Canada') and
product in ('Slipper', 'Sandal');
title 'Before -- Tabulate';
class region product;
var sales;
table region * product ,
sales * sum;
run;

proc report data=work.tabout nowd;
title ' After Proc Tabulate--using OUT= dataset';
column region product sales_sum ;
rbreak after / summarize;
run;

[/pre]
N/A
Posts: 0

Re: Repeating Lines in Output for either Proc Tabulate/Proc Report

Hi Thanks for your inputs.

Somehow due to the everchanging values, not sure if datasets works in my situation?

I did tried using DISPLAY. However, when i use display,same grouping at the ACROSS grouping will not group together, it will display all lines individually.
for example;
value1___value2____c1_____c2____c3
apple____fifi_______55_____________
apple____fifi______________77______
apple____fifi____________________33
pine_____lala______55_____________
pine_____lala____________56______
pine_____lulu___________________66


I ended up using the codes as below. The output won't repeat lines (DISPLAY) but at least the ACROSS Grouping is working.
for example;
value1___value2____c1_____c2____c3
apple____fifi_______55_____77____33
pine_____lala______55_____56______
________lulu___________________66

Any idea of how i can DISPLAY and also have the ACROSS Grouping working?
for example;
value1___value2____c1_____c2____c3
apple____fifi_______55_____77____33
pine_____lala______55_____56______
pine________lulu___________________66

My Code is as below;
PROC REPORT DATA=BP1 nowindows missing headskip split='*';
columns br_zsite_sc_name br_zsite_sc_city br_zsite_sc_state br_zmatl_zprdhier3 br_zmatl_zprdhier5 calquarter, (sum_of_br_us_qlist sum_of_br_us_zeendcpp savings qtd)
("Year-To_Date Total" sum_of_br_us_qlist=ytdsum_of_br_us_qlist sum_of_br_us_zeendcpp=ytdsum_of_br_us_zeendcpp savings=ytdsavings);

define br_zsite_sc_name / group 'Dealer';
define br_zsite_sc_city / group 'City';
define br_zsite_sc_state / group 'State';
define br_zmatl_zprdhier3 / group 'Product Category';
define br_zmatl_zprdhier5 / group 'Product Line';
define calquarter / across ' ';
define sum_of_br_us_qlist / format=dollar12. 'Quoted List $';
define sum_of_br_us_zeendcpp / format=dollar12. 'Est Cust Purch Price';
define savings / format=dollar12. '$ Savings Off List';
define qtd / format = percent7.0 '% Off Qtd List';
define ytdsum_of_br_us_qlist / sum format=dollar12. 'YTD Quoted List $';
define ytdsum_of_br_us_zeendcpp / sum format=dollar12. 'YTD Est Cust Purch Price';
define ytdsavings / sum format=dollar12. 'YTD $ Savings Off List';

rbreak after / summarize ul ol;
title1 "BP &year Purchases by Calendar Quarter ";
run;

Thanks a Ton!
SAS Super FREQ
Posts: 8,719

Re: Repeating Lines in Output for either Proc Tabulate/Proc Report

Hi:
Using an ACROSS item would make a difference in the technique. There are 2 techniques to use as shown below:
1) Make 2 passes of PROC REPORT. Create an output dataset using ACROSS for the first pass and then use PROC REPORT on that output dataset -- labelling the absolute column numbers in this method may be cumbersome.;
OR
2) Use one PROC REPORT step and use COMPUTE blocks and the ALIAS technique to create a "dummy" item with the blank row headers filled in.

There are pros and cons to each method. Method 1 is simpler, but requires 2 PROC REPORT steps. Method 2 is more complex. It requires only 1 REPORT step, but is more advanced syntax. You have to decide which method suits your needs better. If you have the possibility of differing ACROSS values, from run to run then method 2 is the most flexible, as it does not require you to know the ACROSS values in advance.

cynthia
[pre]
** Example 1: Create Output dataset with ACROSS;
proc report data=sashelp.shoes nowd out=accout;
where region in ('Asia', 'Canada') and
product in ('Slipper', 'Sandal', 'Boot');
title '1a) Create output dataset with across and group usages';
column region subsidiary product,sales ;
define region / group noprint;
define subsidiary / group;
define product / across ;
define sales / sum 'Sales';
run;

** Final report based on output dataset from 1a;
proc report data=accout nowd;
title '1b) ACCOUT Data set';
column region subsidiary _c3_ _c4_ _c5_;
define region / display;
define subsidiary / display;
define _c3_ / sum 'Boot';
define _c4_ / sum 'Sandal';
define _c5_ / sum 'Slipper';
rbreak after /summarize;
compute after;
region = 'All Regions';
endcomp;
run;

** Example 2: Use Alias Technique in One PROC REPORT step;
proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Canada') and
product in ('Slipper', 'Sandal', 'Boot');
title '2) Use Alias Technique and Computed Items';
column region showreg subsidiary sales,product ;
define region / group noprint;
define showreg / computed;
define subsidiary / group;
define product / across ;
define sales / sum 'Sales';
rbreak after / summarize;
compute before region;
holdreg = region;
endcomp;
compute showreg / character length=25;
showreg = holdreg;
if _break_ = '_RBREAK_' then showreg = 'All Regions';
endcomp;
run;

[/pre]
N/A
Posts: 0

Re: Repeating Lines in Output for either Proc Tabulate/Proc Report

Cynthia...thanks! U r the best Smiley Very Happy
Ask a Question
Discussion stats
  • 5 replies
  • 124 views
  • 0 likes
  • 3 in conversation