DATA Step, Macro, Functions and more

How to fill the repetitive value in Pro Tabulate statement

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 44
Accepted Solution

How to fill the repetitive value in Pro Tabulate statement

 

FROM THE RESULT TABLE BELOW, HOW TO FILL THE REPETITIVE VALUE IN COLUMN A IN THE PRO BABULATE STATEMENT? IN THIS EXAMPLE, THE REPETITIVE VALUE WOULD BE "NEW YORK" IN FROM CELL A4 TO A7.

 

PROC TABULATE DATA=DATABASE.NY ;

CLASS STATE CITY;

VAR PROFIT EXPENSE;

TABLE (STATE=""*(CITY="")ALL="TOTAL"),PROFIT EXPENSE

/BOX="STATE";

RUN;

 

 ABCD
1STATE PROFITEXPENSE
2  SumSum
3NEW YORKNEW YORK CITY2091558
4 BROOKLYN304909
5 STATEN ISLAND1127615
6 BRONX2285460
7 QUEEN1143827
 TOTAL 69503369

Accepted Solutions
Solution
‎06-13-2016 09:39 PM
SAS Super FREQ
Posts: 8,862

Re: How to fill the repetitive value in Pro Tabulate statement

Hi:

  This is the default behavior from PROC REPORT:

proc_report1.png

 

  You can make a COMPUTED item in PROC REPORT to "copy" the variable of interest (in my example, PRODUCT) down all the other report rows for the group. This is the output produced by the program below.

proc_report_fill_row_rest_output.png

 

There is also an additonal #3 example that shows adding a summary line under each group.

 

cynthia

 


** make small subset;
proc sort data=sashelp.shoes out=newshoes;
  by product region;
  where product in ('Boot' 'Slipper') and
        region in ('Asia' 'Canada' 'Western Europe');
run;
  
ods html file='c:\temp\fill_rows_example.html';
proc report data=newshoes;
  title '1) default behavior';
  column product region sales inventory returns;
  define product / group f=$14.;
  define region / group;
run;
   
proc report data=newshoes;
  title '2a) fill each row example using a COMPUTED item';
  column product showprod region sales inventory returns;
  define product / group f=$14.;
  define showprod / computed f=$14. 'Product';
  define region / group;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
  endcomp;
run;
   
proc report data=newshoes;
  title '2b) final report -- using NOPRINT for Group var PRODUCT';
  column product showprod region sales inventory returns;
  define product / group f=$14. noprint;
  define showprod / computed f=$14. 'Product';
  define region / group;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
  endcomp;
run;

proc report data=newshoes;
  title '3) With Summary Line';
  column product showprod region sales inventory returns;
  define product / group f=$14. noprint;
  define showprod / computed f=$14. 'Product';
  define region / group;
  break after product / summarize style=Header;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
	if upcase(_break_) = 'PRODUCT' then
	  showprod = catx(' ', showprod,'Total');
  endcomp;
run;

ods html close;
title;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How to fill the repetitive value in Pro Tabulate statement

Proc tabulate doesn't have an option for that.

You may consider creating an output data set from Tabulate and then manipulating that set and then print the result. Or perhaps proc report.

SAS Super FREQ
Posts: 8,862

Re: How to fill the repetitive value in Pro Tabulate statement

Hi:
In addition to BallardW's suggestions, you can use PROC REPORT to get what you want.
cynthia
Contributor LL5
Contributor
Posts: 44

Re: How to fill the repetitive value in Pro Tabulate statement

Posted in reply to Cynthia_sas

Thanks Ballard and Cynthia. How to use Proc Report to do that?

Apperciated for your helps!

 

Solution
‎06-13-2016 09:39 PM
SAS Super FREQ
Posts: 8,862

Re: How to fill the repetitive value in Pro Tabulate statement

Hi:

  This is the default behavior from PROC REPORT:

proc_report1.png

 

  You can make a COMPUTED item in PROC REPORT to "copy" the variable of interest (in my example, PRODUCT) down all the other report rows for the group. This is the output produced by the program below.

proc_report_fill_row_rest_output.png

 

There is also an additonal #3 example that shows adding a summary line under each group.

 

cynthia

 


** make small subset;
proc sort data=sashelp.shoes out=newshoes;
  by product region;
  where product in ('Boot' 'Slipper') and
        region in ('Asia' 'Canada' 'Western Europe');
run;
  
ods html file='c:\temp\fill_rows_example.html';
proc report data=newshoes;
  title '1) default behavior';
  column product region sales inventory returns;
  define product / group f=$14.;
  define region / group;
run;
   
proc report data=newshoes;
  title '2a) fill each row example using a COMPUTED item';
  column product showprod region sales inventory returns;
  define product / group f=$14.;
  define showprod / computed f=$14. 'Product';
  define region / group;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
  endcomp;
run;
   
proc report data=newshoes;
  title '2b) final report -- using NOPRINT for Group var PRODUCT';
  column product showprod region sales inventory returns;
  define product / group f=$14. noprint;
  define showprod / computed f=$14. 'Product';
  define region / group;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
  endcomp;
run;

proc report data=newshoes;
  title '3) With Summary Line';
  column product showprod region sales inventory returns;
  define product / group f=$14. noprint;
  define showprod / computed f=$14. 'Product';
  define region / group;
  break after product / summarize style=Header;
  compute before product;
    length holdval $25;
	holdval = product;
  endcomp;
  compute showprod/character length=25;
    showprod = holdval;
	if upcase(_break_) = 'PRODUCT' then
	  showprod = catx(' ', showprod,'Total');
  endcomp;
run;

ods html close;
title;
Contributor LL5
Contributor
Posts: 44

Re: How to fill the repetitive value in Pro Tabulate statement

Thanks cynthia!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 251 views
  • 3 likes
  • 3 in conversation