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 |
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;
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.
Thanks Ballard and Cynthia. How to use Proc Report to do that?
Apperciated for your helps!
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;
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.
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.