Solved
Contributor
Posts: 55

# 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;

 A B C D 1 STATE PROFIT EXPENSE 2 Sum Sum 3 NEW YORK NEW YORK CITY 2091 558 4 BROOKLYN 304 909 5 STATEN ISLAND 1127 615 6 BRONX 2285 460 7 QUEEN 1143 827 TOTAL 6950 3369

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

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

Hi:

This is the default behavior from PROC REPORT:

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.

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;``````

All Replies
Super User
Posts: 13,508

## 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: 9,365

## 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
Posts: 55

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

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

Solution
‎06-13-2016 09:39 PM
SAS Super FREQ
Posts: 9,365

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

Hi:

This is the default behavior from PROC REPORT:

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.

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
Posts: 55

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

Thanks cynthia!
🔒 This topic is solved and locked.